package com.dhcc.DRGsHosp.application.impl;

import java.util.List;
import java.util.Map;

import javax.inject.Named;

import org.dayatang.domain.EntityRepository;
import org.dayatang.domain.InstanceFactory;
import org.dayatang.querychannel.JdbcTemplateObj;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.transaction.annotation.Transactional;

import com.dhcc.DRGsHosp.application.BZAnalysisDRGsReportApplication;
import com.dhcc.DRGsHosp.infra.util.Arith;
import com.dhcc.DRGsHosp.infra.util.DrgsIndex;
/**
 * 
 * ClassName: AnalysisDRGsReportApplicationImpl  
 * Description ：简版新版分析报告导出-POI技术   
 * Reason: TODO ADD REASON(可选).   
 * date: 2017年11月7日 下午4:37:30  
 * @author ShaozhiYuan
 * @version V1.0 DHC
 */
@Named
@Transactional 
public class BZAnalysisDRGsReportApplicationImpl implements BZAnalysisDRGsReportApplication{
    @Autowired  
    private JdbcTemplateObj jdbcTemplateObj;
    
    private EntityRepository repository;
    
	private EntityRepository getRepository() {
		if (repository == null) {
			repository = InstanceFactory.getInstance(
					EntityRepository.class, "repository");
		}
		return repository;
	}
    
	/**
	 * 
	 * @Description:获取当前系统中可用标杆  
	 * @return
	 */
	@Override
	public  List<Map<String,Object>> getARBenchmarkYear() {
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT DISTINCT(a.AS_YEAR) AS_YEAR FROM drgs.t_annualstd a ORDER BY a.AS_YEAR DESC \n ");	
		 List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ;
		return list;
	}
	/**
	 *获取当前系统库中所有的临床专业
	 */
	@Override
	public List<Map<String, Object>> getARBenchSpecCode() {
		StringBuilder sql = new StringBuilder();
		sql.append(" select  distinct cli.spec_desc DECC, cli.spec_code CODE  from  DRGS.T_DEPARTMENTDRG t left join drgs.t_clispec cli   on  CLI.SPEC_CODE = t.parspec_code  \n ");	
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ;
		return list;
	}
	/**
	 * 
	 * TODO 看日期区间内是否有数据  
	 * 
	 * @author ShaoZhiYuan  
	 * @see com.dhcc.DRGsHosp.application.AnalysisDRGsReportApplication#findARDataIsNull(java.lang.String, java.lang.String, java.lang.String)
	 */
	@Override
	public int findARDataIsNull(String dataFrom, String dataTo, String drgVersion,String hospCode) {
		StringBuffer sql= new StringBuffer();
		sql.append(" SELECT COUNT(p.PARID) FROM drgs.MR_IPINFO mr LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=mr.PMAININDEX RIGHT JOIN drgs.t_patientdrg p ON mr.PMAININDEX=p.PARID  \n");
		sql.append(" WHERE instr('"+hospCode+"',BA.p00010001)>0 "+Arith.mrAROutDateSql(dataFrom,dataTo,"mr" )+" \n");
		sql.append(" and p.ver_code='"+drgVersion+"'\n");
		return jdbcTemplateObj.queryForObject(sql.toString(), Integer.class);
	}
	/**
	 * 
	 * TODO 获取新增病组
	 * 
	 * @author ShaoZhiYuan  
	 * @see com.dhcc.DRGsHosp.application.AnalysisDRGsReportApplication#findARNewDrgs(java.lang.String, java.lang.String, java.lang.String, java.lang.String)
	 */
	@Override
	public List<Map<String,Object>> findARNewDrgs(String dateFrom, String dateTo, String benchmarkYear, String drgVersion, String hospCode) {
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT  tp.PARDRG_CODE PARDRG_CODE ,COUNT(tp.PARDRG_CODE) PARDRG_CNT FROM  \n");
		sql.append("(select mr.PMAININDEX ID from drgs.MR_IPINFO mr LEFT JOIN DRGS.MR_BASEINFO BA ON BA.PMAININDEX=mr.PMAININDEX WHERE instr('"+hospCode+"',BA.p00010001)>0 and mr.P00320000 <61  "+Arith.mrAROutDateSql(dateFrom,dateTo,"mr" )+" ) tmr \n");
		sql.append(" JOIN (select p.PARID,p.PARDRG_CODE from drgs.t_patientdrg p where p.ver_region='"+drgVersion+"' and  p.PARDRG_CODE is NOT NULL and  NOT EXISTS \n");
		sql.append("(SELECT AS_DRG_CODE FROM drgs.t_annualstd ad WHERE p.PARDRG_CODE=ad.as_drg_code  AND ad.as_weight is not null and ad.AS_ID='"+benchmarkYear+"' and ad.VER_CODE='"+drgVersion+"'))tp  \n");
		sql.append(" ON tmr.ID=tp.PARID  \n");
		sql.append(" GROUP BY tp.PARDRG_CODE order by tp.PARDRG_CODE  \n");
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString());
		return list;
	}
	/**
	 * 
	 * TODO 校验数据完整性
	 * 
	 * @author ShaoZhiYuan  
	 * @see com.dhcc.DRGsHosp.application.AnalysisDRGsReportApplication#findARCheckedData(java.lang.String, java.lang.String, java.lang.String, java.lang.String)
	 */
	@Override
	public  List<Map<String,Object>>  findARCheckedData(String dateFrom, String dateTo, String benchmarkYear, String drgVersion, String hospCode) {
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT decode(SUM(CASE WHEN op.POTYPE='M' THEN 1 else 0 end),0,0,SUM(CASE WHEN op.PO0301 is NOT null AND op.POTYPE='M' THEN 1 else 0 end)/SUM(CASE WHEN op.POTYPE='M' THEN 1 else 0 end)) FIRST_JUDGE_VALUE, \n");
		sql.append(" decode(SUM(CASE WHEN op.POTYPE='M' THEN 1 else 0 end),0,0,SUM(CASE WHEN op.PO0101 is NOT null AND op.POTYPE='M'  THEN 1 else 0 end)/SUM(CASE WHEN op.POTYPE='M' THEN 1 else 0 end)) TWO_JUDGE_VALUE , \n");
		sql.append(" decode(COUNT(op.PO0101),0,0,SUM(CASE WHEN op.PO0301 is not null  THEN 1 else 0 end)/COUNT(op.PO0101)) THREE_JUDGE_VALUE, \n");
		sql.append(" decode(SUM(CASE WHEN di.PDTYPE ='M' THEN 1 else 0 end),0,0,SUM(CASE WHEN di.PDTYPE is NOT null AND di.PDTYPE ='M' THEN 1 else 0 end)/SUM(CASE WHEN di.PDTYPE ='M' THEN 1 else 0 end)) FOUR_JUDGE_VALUE, \n");
		sql.append(" decode(SUM(CASE WHEN di.PDTYPE ='M' THEN 1 else 0 end),0,0,SUM(CASE WHEN di.PD0101 is NOT null AND di.PDTYPE ='M' THEN 1 else 0 end)/SUM(CASE WHEN di.PDTYPE ='M' THEN 1 else 0 end)) FIVE_JUDGE_VALUE\n");
		sql.append(" from drgs.MR_IPINFO mr LEFT JOIN DRGS.MR_BASEINFO BA ON BA.PMAININDEX=mr.PMAININDEX \n");
		sql.append(" LEFT JOIN drgs.mr_operation op on mr.PMAININDEX=op.PMAININDEX \n");
		sql.append(" LEFT  JOIN  drgs.MR_DIAGNOSE  di on mr.PMAININDEX=di.PMAININDEX\n");
		sql.append(" where instr('"+hospCode+"',BA.p00010001)>0  "+Arith.mrAROutDateSql(dateFrom,dateTo,"mr")+"  \n");
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ;
		return list;
	}
	/**
	 * 
	 * TODO 查看该时间段内的科室总数
	 * 
	 * @author ShaoZhiYuan  
	 * @see com.dhcc.DRGsHosp.application.AnalysisDRGsReportApplication#findARDepCount()
	 */
	@Override
	public int findARDepCount() {
		StringBuilder sql = new StringBuilder();	
		sql.append(" SELECT COUNT(1) FROM drgs.t_department \n");	
		return jdbcTemplateObj.queryForObject(sql.toString(), Integer.class);
	}
	/**
	 * 
	 * TODO 获取医院基本信息
	 * 
	 * @author ShaoZhiYuan  
	 * @see com.dhcc.DRGsHosp.application.AnalysisDRGsReportApplication#findARSimpHospBaseQuota()
	 */
	@Override
	public List<Map<String, Object>> findARSimpHospBaseQuota(String drgVersion,String dateFrom,String dateTo, String hospCode) {
		
		StringBuffer sql= new StringBuffer();
		sql.append(" select X.allcnt ALLCNT ,X.ingroup INGROUP,X.over60 OVERSIXTY,ROUND(decode(X.allcnt-X.over60,0,0,X.ingroup*100/(X.allcnt-X.over60)),2) GROUPRATIO ,X.notingroup NOTINGROUP \n");
		sql.append(" FROM ( select COUNT(mr.PMAININDEX) allcnt,SUM(CASE WHEN mr.P00320000 > 60 THEN 1 else 0 end  ) over60, \n");
		sql.append(" sum(CASE WHEN mr.P00320000 <61 AND p.PARDRG_CODE is not NULL and p.VER_CODE='"+drgVersion+"' THEN 1 else 0 END ) ingroup, \n");
		sql.append(" sum(CASE WHEN mr.P00320000 <61 AND p.PARDRG_CODE is  NULL  THEN 1 else 0 END ) notingroup \n");
		sql.append(" from drgs.MR_IPINFO mr LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=mr.PMAININDEX left join drgs.t_patientdrg p on mr.PMAININDEX= p.PARID \n");
		sql.append(" where instr('"+hospCode+"',BA.p00010001)>0 "+Arith.mrAROutDateSql(dateFrom,dateTo,"mr")+" ) X \n");
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ;
		return list;
	}
	/**
	 * 
	 * TODO 获取医院基本信息 表1
	 * 
	 * @author ShaoZhiYuan  
	 * @see com.dhcc.DRGsHosp.application.AnalysisDRGsReportApplication#findSimpHospBaseTableTwo(java.util.Map)
	 */
	@Override
	public List<Map<String, Object>> findSimpHospBaseTableTwo(String dateFrom,String dateTo, String hospCode) {
		  
		StringBuffer sql= new StringBuffer();
		sql.append(" SELECT a.allcnt ALLCNTTWO,ROUND(decode(a.allcnt,0,0,a.COSTAMOUNT/a.allcnt),2) hospavgfee,ROUND(decode(a.allcnt,0,0,a.dayscnt/a.allcnt),2) hospavgdays, \n");
		sql.append(" ROUND(decode(a.COSTAMOUNT,0,0,a.MATERCOST*100/a.COSTAMOUNT),2) hospmaterratio, \n");
		sql.append(" ROUND(decode(a.COSTAMOUNT,0,0,a.DRUGCOST*100/a.COSTAMOUNT),2) hospdrugratio, ROUND(decode(a.allcnt,0,0,a.CASEDEAD*100/a.allcnt),2) hospdeadratio FROM ( \n");
		sql.append(" SELECT COUNT(mr.PMAININDEX) allcnt ,SUM(mr.P00320000) dayscnt ,sum(mr.P00800000) COSTAMOUNT,\n");
		sql.append(" sum(exp.DRUG_COST) DRUGCOST ,sum(exp.mater_cost)MATERCOST, SUM(CASE WHEN mr.P00620001 = 5 THEN 1 ELSE 0 END) CASEDEAD \n");
		sql.append(" FROM drgs.MR_IPINFO mr LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=mr.PMAININDEX left join drgs.MR_EXPENSE_SUM exp on mr.PMAININDEX=exp.PMAININDEX where  \n");
		sql.append(" instr('"+hospCode+"',BA.p00010001)>0 "+Arith.mrAROutDateSql(dateFrom,dateTo,"mr")+")  a\n");
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ;
		return list;
	}
	/**
	 * 
	 * TODO 表格2 获取cmi，时间消耗指数，费用消耗指数，drg组数,总权重
	 * 
	 * @author ShaoZhiYuan  
	 * @see com.dhcc.DRGsHosp.application.AnalysisDRGsReportApplication#findSimpHospBaseTableTwo(java.util.Map)
	 */
	@Override
	public List<Map<String, Object>> findSimpHospBaseTableThree(String drgVersion,String dateFrom,String dateTo,String benchmarkYear, String hospCode) {
		StringBuffer sql= new StringBuffer();
		sql.append("  SELECT COUNT(DISTINCT(p.PARDRG_CODE)) hospdrgcnt,ROUND(decode(COUNT(p.PARID),0,0,SUM(a.AS_WEIGHT)/COUNT(p.PARID)),2) hospcmi ,SUM(a.AS_WEIGHT) hosptotalweight,\n");
		sql.append("  COUNT(p.PARID) hospingroupcnt ,round(decode(COUNT(p.parid),0,0,sum(decode(a.AS_AVG_FEE,0,0,mr.P00800000/a.AS_AVG_FEE))/COUNT(p.parid)),2)  hospeffee, \n");
		sql.append("  round(decode(COUNT(p.parid),0,0,sum(decode(a.AS_AVG_DAYS,0,0,mr.P00320000/a.AS_AVG_DAYS))/COUNT(p.parid)),2) hosptimefee, \n");
		sql.append("  round(sum(case when mr.P00620001='5' and a.AS_RISK_LEVEL='1' then 1 else 0 END)/sum(case WHEN a.AS_RISK_LEVEL='1' then 1 else 0 END)*100,2) deadriskAratio \n");
		sql.append("  FROM drgs.MR_IPINFO mr LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=mr.PMAININDEX LEFT JOIN  drgs.t_patientdrg p ON mr.PMAININDEX=p.PARID LEFT JOIN   drgs.t_annualstd a ON p.PARDRG_CODE=a.AS_DRG_CODE \n");
		sql.append("  WHERE instr('"+hospCode+"',BA.p00010001)>0 and p.ver_code='"+drgVersion+"' and p.PARDRG_CODE is not null AND a.AS_ID='"+benchmarkYear+"' and mr.P00320000 <61  and a.as_weight is not null  \n");
		sql.append("  "+Arith.mrAROutDateSql(dateFrom,dateTo,"mr")+" \n");	
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ;
		return list;
	}
	/**
	 * 
	 * TODO 获取手术病例
	 * 
	 * @author ShaoZhiYuan  
	 * @see com.dhcc.DRGsHosp.application.AnalysisDRGsReportApplication#findHospCaseOperationCount(java.util.Map)
	 */
	@Override
	public String findHospCaseOperationCount(Map<String, Object> map) {
		String dateFrom = map == null?"":map.get("dateFrom").toString();
		String dateTo = map == null?"":map.get("dateTo").toString();
		String hospCode = map == null?"":map.get("hospCode").toString(); //获得院区信息
		String sqlResult = "SELECT COUNT(A.PMAININDEX)  FROM DRGS.MR_IPINFO B LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX= B.PMAININDEX JOIN DRGS.MR_OPERATION A ON A.PMAININDEX = B.PMAININDEX WHERE instr('"+hospCode+"',BA.p00010001)>0 and A.PO1400 ='1'  and  A.POTYPE='M' "+Arith.mrAROutDateSql(dateFrom,dateTo,"B");
		String caseOperationCount = jdbcTemplateObj.queryForObject(sqlResult.toString(), String.class);
		return caseOperationCount;
	}
	/**
	 * 获取手术版本
	 */
	@Override
	public String findHospCaseOperationVersion(Map<String, Object> map) {
		String sqlResult = " select t.cfg_value from DRGS.T_CONFIG t where t.CFG_CODE='OPER_VERSION' ";
		String caseOperationCount = jdbcTemplateObj.queryForObject(sqlResult.toString(), String.class);
		return caseOperationCount;
	}
	/**
	 * 获取该手术版本信息下的字典数
	 */
	@Override
	public String findHospCaseOperVersionCnt(Map<String, Object> map) {
		String operVersion = map == null?"":map.get("operVersion").toString().trim();
		String sqlResult = " select count(1) from drgs.T_ICD_BJ_OPER t where t.oicd_version='"+operVersion+"' ";
		String caseOperationCount = jdbcTemplateObj.queryForObject(sqlResult.toString(), String.class);
		return caseOperationCount;
	}
	 /**
	  *更新手术操作字段
	  */
    @Override
	public void updateHospCaseOper(Map<String, Object> paramMap) {
    	String operVersion = paramMap == null?"":paramMap.get("operVersion").toString().trim();
    	String sql = "";
    	sql+=" UPDATE drgs.MR_OPERATION op set op.PO1400=( select io.is_oper  from drgs.T_ICD_BJ_OPER io ";
    	sql+=" where io.oicd_code=op.PO0101 and  io.oicd_version='"+operVersion+"' ) where op.potype='M' ";
    	getRepository().createSqlQuery(sql).executeUpdate();
	}
	/**
	 * TODO 获取手术级别及病例数等指标
	 */
	@Override
	public List<Map<String, Object>> findOperCaseByLvel(Map<String, Object> map) {
		String dateFrom = map == null?"":map.get("dateFrom").toString();
		String dateTo = map == null?"":map.get("dateTo").toString();
		String drgVersion = map == null?"":map.get("drgVersion").toString();
		String benchmarkYear = map == null?"":map.get("benchmarkYear").toString();
		String hospCode = map == null?"":map.get("hospCode").toString(); //获得院区信息
		StringBuffer sql= new StringBuffer();
		sql.append(" select  OP.PO0301 opercaselevel,COUNT(OP.PMAININDEX) opercasecnt,sum(case when pd.pardrg_code is not null then 1 else 0 end) drgopercnt, \n");
		sql.append(" sum(case when pd.pardrg_code is null and mr.P00320000 <61 then 1 else 0 end) nodrgopercnt, \n");
	    sql.append(" round(sum(a.as_weight)/sum(case when pd.pardrg_code is not null then 1 else 0 end),2) cmi, \n");
	    sql.append(" sum(a.as_weight) as_weight from drgs.mr_ipinfo mr \n");
		sql.append(" left join drgs.mr_operation op on op.PO1400 ='1' and mr.PMAININDEX=OP.PMAININDEX and OP.POTYPE='M' \n");
		sql.append(" left join drgs.MR_BASEINFO BA on BA.PMAININDEX=MR.PMAININDEX and instr('"+hospCode+"', BA.p00010001)>0 \n");
	    sql.append(" left join drgs.t_patientdrg pd on pd.parid=mr.pmainindex and pd.ver_code='"+drgVersion+"' \n");
	    sql.append(" LEFT JOIN drgs.t_annualstd a ON pd.PARDRG_CODE=a.AS_DRG_CODE  AND a.AS_ID='"+benchmarkYear+"' \n");
		sql.append(" where OP.PO0301 is not null "+Arith.mrAROutDateSql(dateFrom,dateTo,"mr")+" \n");
		sql.append(" GROUP BY OP.PO0301 ORDER BY COUNT(OP.PMAININDEX) DESC \n");
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ;
		return list;
	}
	/**
	 * 手术种类总数
	 */
	@Override
	public String findOperTypeCnt(Map<String, Object> map) {
		String dateFrom = map == null?"":map.get("dateFrom").toString();
		String dateTo = map == null?"":map.get("dateTo").toString();
		String hospCode = map == null?"":map.get("hospCode").toString(); //获得院区信息
		String sqlResult = "SELECT COUNT(DISTINCT B.PO0101) FROM drgs.MR_IPINFO a LEFT JOIN drgs.MR_OPERATION b on a.PMAININDEX=b.PMAININDEX LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=a.PMAININDEX WHERE instr('"+hospCode+"',BA.p00010001)>0 and b.PO1400='1' and b.POTYPE='M' "+Arith.mrAROutDateSql(dateFrom,dateTo,"a");
		String caseOperTypeCnt = jdbcTemplateObj.queryForObject(sqlResult.toString(), String.class);
		return caseOperTypeCnt;
	}
	/**
	 * 各科室手术的总数
	 */
	@Override
	public List<Map<String, Object>> findDepMainOperInfo(Map<String, Object> map) {
		String dateFrom = map == null?"":map.get("dateFrom").toString();
		String dateTo = map == null?"":map.get("dateTo").toString();
		String hospCode = map == null?"":map.get("hospCode").toString(); //获得院区信息
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT a.p00300001 depCode,a.p00300000 depName, COUNT(B.PMAININDEX) OPERCNT FROM drgs.MR_IPINFO a \n ");	
		sql.append(" LEFT JOIN drgs.MR_OPERATION b on a.PMAININDEX=b.PMAININDEX LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=a.PMAININDEX left join drgs.t_department c on a.p00300001=c.dep_code\n ");	
		sql.append(" WHERE instr('"+hospCode+"',BA.p00010001)>0 and b.PO1400='1' and b.POTYPE='M' and c.is_ope_dep='1' "+Arith.mrAROutDateSql(dateFrom,dateTo,"a")+" \n ");	
		sql.append(" GROUP BY a.p00300001,a.p00300000 ORDER BY OPERCNT DESC  \n ");
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ;
		return list;
	}
	/**
	 * 各科室出现次数最多的前三位主要手术名称
	 */
	@Override
	public List<Map<String, Object>> findtopOperCaseByDep(Map<String, Object> map, String depCode) {
		String dateFrom = map == null?"":map.get("dateFrom").toString();
		String dateTo = map == null?"":map.get("dateTo").toString();
		String hospCode = map == null?"":map.get("hospCode").toString(); //获得院区信息
		StringBuilder sql = new StringBuilder();
		sql.append("  SELECT * FROM (SELECT a.p00300000 depName,b.PO0101 CODE, b.PO0100 DECC, COUNT(B.PMAININDEX) CNT FROM drgs.MR_IPINFO a LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=a.PMAININDEX LEFT JOIN drgs.MR_OPERATION b on a.PMAININDEX=b.PMAININDEX \n");
		sql.append("  WHERE instr('"+hospCode+"',BA.p00010001)>0 and b.PO1400='1' and b.POTYPE='M' and a.p00300001='"+depCode+"' \n");
		sql.append("  "+Arith.mrAROutDateSql(dateFrom,dateTo,"a")+"   \n");
		sql.append("  GROUP BY a.p00300000,b.PO0101, b.PO0100 ORDER BY CNT DESC)where rownum<4  \n");
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ;
		return list;
	}
	/**
	 * 获取DRG病组,内科组，外科组，操作组
	 */
	@Override
	public List<Map<String, Object>> findHospDRGsInfo(Map<String, Object> map) {
		String dateFrom = map == null?"":map.get("dateFrom").toString();
		String dateTo = map == null?"":map.get("dateTo").toString();
		String drgVersion = map == null?"":map.get("drgVersion").toString();
		String hospCode = map == null?"":map.get("hospCode").toString(); //获得院区信息
		StringBuffer sql= new StringBuffer();
		sql.append("	SELECT  (AZ.totaldrg) ALLDRGS,AX.WAIKE WKDRGS,AX.NEIKE NKDRGS,AX.CAOZUO CZDRGS ,AY.WAIKEY WKCNT,AY.NEIKEY NKCNT,AY.CAOZUOY CZCNT FROM (  \n ");
		sql.append("	SELECT  SUM(case WHEN SUBSTR(X.drgcode,2,1)='A' OR SUBSTR(X.drgcode,2,1)='B' OR SUBSTR(X.drgcode,2,1)='C' \n ");
		sql.append("	 OR SUBSTR(X.drgcode,2,1)='D' OR SUBSTR(X.drgcode,2,1)='E' OR SUBSTR(X.drgcode,2,1)='F' OR SUBSTR(X.drgcode,2,1)='G' \n ");
		sql.append("	 OR SUBSTR(X.drgcode,2,1)='H' OR SUBSTR(X.drgcode,2,1)='J'  THEN 1 ELSE 0 END) AS WAIKE,  \n ");
		sql.append("	SUM(case WHEN SUBSTR(X.drgcode,2,1)='K' OR SUBSTR(X.drgcode,2,1)='L' OR SUBSTR(X.drgcode,2,1)='M'   \n ");
		sql.append("	OR SUBSTR(X.drgcode,2,1)='N' OR SUBSTR(X.drgcode,2,1)='P' OR SUBSTR(X.drgcode,2,1)='Q'  THEN 1 ELSE 0 END) AS CAOZUO,  \n ");
		sql.append("	SUM(case WHEN SUBSTR(X.drgcode,2,1)='R' OR SUBSTR(X.drgcode,2,1)='S' OR SUBSTR(X.drgcode,2,1)='T' OR SUBSTR(X.drgcode,2,1)='U'  \n ");
		sql.append("	OR SUBSTR(X.drgcode,2,1)='V' OR SUBSTR(X.drgcode,2,1)='W' OR SUBSTR(X.drgcode,2,1)='X'  \n ");
		sql.append("	OR SUBSTR(X.drgcode,2,1)='Y' OR SUBSTR(X.drgcode,2,1)='Z'  THEN 1 ELSE 0 END) AS NEIKE  \n ");
		sql.append("	FROM (SELECT DISTINCT p.PARDRG_CODE  as drgcode from drgs.MR_IPINFO mr LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=mr.PMAININDEX LEFT JOIN drgs.t_patientdrg p on mr.PMAININDEX=p.PARID \n ");
		sql.append("	 where  instr('"+hospCode+"',BA.p00010001)>0 and p.PARDRG_CODE is not null and p.VER_CODE='"+drgVersion+"' "+Arith.mrAROutDateSql(dateFrom,dateTo,"mr")+" \n ");
		sql.append("	)X)AX ,(SELECT SUM(case WHEN SUBSTR(Y.drgcode,2,1)='A' OR SUBSTR(Y.drgcode,2,1)='B' OR SUBSTR(Y.drgcode,2,1)='C' \n ");
		sql.append("	 OR SUBSTR(Y.drgcode,2,1)='D' OR SUBSTR(Y.drgcode,2,1)='E' OR SUBSTR(Y.drgcode,2,1)='F' OR SUBSTR(Y.drgcode,2,1)='G'  \n ");
		sql.append("	OR SUBSTR(Y.drgcode,2,1)='H' OR SUBSTR(Y.drgcode,2,1)='J'  THEN 1 ELSE 0 END) AS WAIKEY, \n ");
		sql.append("	 SUM(case WHEN SUBSTR(Y.drgcode,2,1)='K' OR SUBSTR(Y.drgcode,2,1)='L' OR SUBSTR(Y.drgcode,2,1)='M'  \n ");
		sql.append("      OR SUBSTR(Y.drgcode,2,1)='N' OR SUBSTR(Y.drgcode,2,1)='P' OR SUBSTR(Y.drgcode,2,1)='Q'  THEN 1 ELSE 0 END) AS CAOZUOY, \n ");
		sql.append("	SUM(case WHEN SUBSTR(Y.drgcode,2,1)='R' OR SUBSTR(Y.drgcode,2,1)='S' OR SUBSTR(Y.drgcode,2,1)='T'  \n ");
		sql.append("	OR SUBSTR(Y.drgcode,2,1)='U' OR SUBSTR(Y.drgcode,2,1)='V' OR SUBSTR(Y.drgcode,2,1)='W' OR SUBSTR(Y.drgcode,2,1)='X'  \n ");
		sql.append("	OR SUBSTR(Y.drgcode,2,1)='Y' OR SUBSTR(Y.drgcode,2,1)='Z'  THEN 1 ELSE 0 END) AS NEIKEY \n ");
		sql.append("	FROM (SELECT  p.PARDRG_CODE  as drgcode from drgs.MR_IPINFO mr LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=mr.PMAININDEX LEFT JOIN drgs.t_patientdrg p on mr.PMAININDEX=p.PARID  \n ");
		sql.append("	 where  instr('"+hospCode+"',BA.p00010001)>0 and p.PARDRG_CODE is not null and p.VER_CODE='"+drgVersion+"' "+Arith.mrAROutDateSql(dateFrom,dateTo,"mr")+" )Y) AY,  \n ");
		sql.append("    (SELECT count(DISTINCT p.PARDRG_CODE) as totaldrg from drgs.MR_IPINFO mr LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=mr.PMAININDEX LEFT JOIN drgs.t_patientdrg p on mr.PMAININDEX=p.PARID  \n ");
		sql.append("	where  instr('"+hospCode+"',BA.p00010001)>0  and p.PARDRG_CODE is not null and p.VER_CODE='"+drgVersion+"' "+Arith.mrAROutDateSql(dateFrom,dateTo,"mr")+" )AZ  \n ");
		
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ;
		return list;
	}
	/**
	 * DRG病组和病例复杂程度
	 */
	@Override
	public List<Map<String, Object>> findPersonCnt(Map<String, Object> map) {
		String dateFrom = map == null?"":map.get("dateFrom").toString();
		String dateTo = map == null?"":map.get("dateTo").toString();
		String drgVersion = map == null?"":map.get("drgVersion").toString();
		String hospCode = map == null?"":map.get("hospCode").toString(); //获得院区信息
		StringBuilder sql = new StringBuilder();	
		sql.append(" SELECT AX.DRGsType1Num DRGCA,AX.DRGsType3Num DRGCB,AX.DRGsType5Num DRGCC,AY.CaseType1Num CASEA,AY.CaseType3Num CASEB,AY.CaseType5Num CASEC FROM ( \n");
		sql.append(" SELECT SUM(case WHEN SUBSTR(X.drgcode,4,1) in ('1','2') THEN 1 ELSE 0 END) AS DRGsType1Num, \n");
		sql.append(" SUM(case WHEN SUBSTR(X.drgcode,4,1) in ('3','4')  THEN 1 ELSE 0 END) AS DRGsType3Num, \n");
		sql.append(" SUM(case WHEN SUBSTR(X.drgcode,4,1) in ('5','6')  THEN 1 ELSE 0 END) AS DRGsType5Num \n");
		sql.append(" FROM ( \n");
		sql.append("  SELECT DISTINCT p.PARDRG_CODE  as drgcode from drgs.MR_IPINFO mr LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=mr.PMAININDEX LEFT JOIN drgs.t_patientdrg p on mr.PMAININDEX=p.PARID  \n");
		sql.append("  where instr('"+hospCode+"',BA.p00010001)>0 and p.PARDRG_CODE is not null and p.VER_CODE='"+drgVersion+"' "+Arith.mrAROutDateSql(dateFrom,dateTo,"mr")+" \n");
		sql.append("  )X \n");
		sql.append(" )AX ,\n");
		sql.append(" (SELECT SUM(case WHEN SUBSTR(Y.drgcode,4,1) in ('1','2')   THEN 1 ELSE 0 END) AS CaseType1Num, \n");
		sql.append("  SUM(case WHEN SUBSTR(Y.drgcode,4,1) in ('3','4')  THEN 1 ELSE 0 END) AS CaseType3Num, \n");
		sql.append(" SUM(case WHEN SUBSTR(Y.drgcode,4,1) in ('5','6')  THEN 1 ELSE 0 END) AS CaseType5Num \n");
		sql.append(" FROM ( \n");
		sql.append(" SELECT  p.PARDRG_CODE  as drgcode from drgs.MR_IPINFO mr LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=mr.PMAININDEX LEFT JOIN drgs.t_patientdrg p on mr.PMAININDEX=p.PARID \n");
		sql.append("  where instr('"+hospCode+"',BA.p00010001)>0 and p.PARDRG_CODE is not null and p.VER_CODE='"+drgVersion+"' "+Arith.mrAROutDateSql(dateFrom,dateTo,"mr")+"\n");
		sql.append("  )Y) AY \n");
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ;
		return list;
	}
	/**
	 * 各MDC专业本期和同期出院病例数分布及总权重
	 */
	@Override
	public List<Map<String, Object>> findMdcCnt(Map<String, Object> map) {
		String dateFrom = map == null?"":map.get("dateFrom").toString();
		String dateTo = map == null?"":map.get("dateTo").toString();
		String dateFromCc = DrgsIndex.getDateFromCc(dateFrom,"year");//起始日期同期
		String dateToCc = DrgsIndex.getDateFromCc(dateTo,"year");//终止日期同期
		String benchmarkYear = map == null?"":map.get("benchmarkYear").toString();
		String hospCode = map == null?"":map.get("hospCode").toString(); //获得院区信息
		StringBuilder sql = new StringBuilder();	
		sql.append(" select a.mdc,a.thisYear,case when b.lastYear is null then 0 else b.lastYear end lastYear,a.totalweight  \n");
		sql.append(" from(select mdc||c.mdc_desc mdc,count(*) thisYear，sum(case when a.pardrg_code is not null then std.as_weight else 0 end) totalweight from drgs.t_patientdrg a \n");
		sql.append(" left join drgs.mr_ipinfo b on a.parid=b.pmainindex left join drgs.t_annualstd std on std.as_drg_code=a.pardrg_code and std.as_id='"+benchmarkYear+"'\n");
		sql.append(" left join drgs.t_mdcgroup c on c.mdc_code=a.mdc and c.ver_code=a.ver_code LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=b.PMAININDEX \n");
		sql.append(" where instr('"+hospCode+"',BA.p00010001)>0 and a.mdc is not null "+Arith.mrAROutDateSql(dateFrom,dateTo,"b")+" \n");
		sql.append(" group by mdc||c.mdc_desc )a left join ( \n");
		sql.append(" select mdc||c.mdc_desc mdc,count(*) lastYear from drgs.t_patientdrg a \n");
		sql.append(" left join drgs.mr_ipinfo b on a.parid=b.pmainindex LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=b.PMAININDEX \n");
		sql.append(" left join drgs.t_mdcgroup c on c.mdc_code=a.mdc and c.ver_code=a.ver_code \n");
		sql.append(" where instr('"+hospCode+"',BA.p00010001)>0 and a.mdc is not null "+Arith.mrAROutDateSql(dateFromCc,dateToCc,"b")+" \n");
		sql.append(" group by mdc||c.mdc_desc )b on a.mdc=b.mdc order by mdc desc \n");
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ;
		return list;
	}
	/**
	 * 科室类型个数
	 */
	@Override
	public List<Map<String, Object>> findDepTypeCnt(Map<String, Object> map) {
		String dateFrom = map == null?"":map.get("dateFrom").toString();
		String dateTo = map == null?"":map.get("dateTo").toString();
		String hospCode = map == null?"":map.get("hospCode").toString(); //获得院区信息
		StringBuilder sql = new StringBuilder();	
		sql.append(" select dep.is_ope_dep depType,count(distinct b.p00300001) depcnt from drgs.t_department dep  \n");
		sql.append(" left join drgs.mr_ipinfo b on b.p00300001=dep.dep_code LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=b.PMAININDEX where instr('"+hospCode+"',BA.p00010001)>0 and dep.is_ope_dep is not null "+Arith.mrAROutDateSql(dateFrom,dateTo,"b")+" group by dep.is_ope_dep order by dep.is_ope_dep \n");
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ;
		return list;
	}
	/**
	 * 
	 * TODO 科室总权重，CMI值，时间消耗指数及费用消耗指数
	 */
	@Override
	public List<Map<String, Object>> findOperDepQuota(Map<String, Object> map,String depType,String quotaType) {
		String dateFrom = map == null?"":map.get("dateFrom").toString();
		String dateTo = map == null?"":map.get("dateTo").toString();
		String drgVersion = map == null?"":map.get("drgVersion").toString();
		String benchmarkYear = map == null?"":map.get("benchmarkYear").toString();
		String hospCode = map == null?"":map.get("hospCode").toString(); //获得院区信息
		StringBuffer sql= new StringBuffer();
		sql.append("  SELECT dep.dep_code CODE, dep.dep_name DECC , \n");
		sql.append("  round(decode(COUNT(G.parid),0,0,sum(decode(G.as_weight,0,0,G.as_weight))),2) TOTALWEIGHT, \n");
		sql.append("  round(decode(COUNT(G.parid),0,0,sum(decode(G.as_weight,0,0,G.as_weight))/COUNT(G.parid)),2) CMI, \n");
		sql.append("  round(decode(COUNT(G.parid),0,0,sum(decode(G.AS_AVG_FEE,0,0,E.P00800000/G.AS_AVG_FEE))/COUNT(G.parid)),2) FEE,  \n");
		sql.append("  round(decode(COUNT(G.parid),0,0,sum(decode(G.AS_AVG_DAYS,0,0,E.P00320000/G.AS_AVG_DAYS))/COUNT(G.parid)),2) TIM \n");
		sql.append("  FROM  DRGS.MR_IPINFO E left join drgs.t_patientdrg pg on E.pmainindex=pg.parid  \n");
		sql.append("  left join drgs.t_department dep on dep.dep_code=e.p00300001 and dep.is_ope_dep='"+depType+"' RIGHT JOIN (  \n");
		sql.append("  select distinct A.PARID,A.PARDRG_CODE,T.AS_AVG_FEE,T.AS_AVG_DAYS,t.as_weight  FROM DRGS.T_PATIENTDRG A \n");
		sql.append("  left join DRGS.T_ANNUALSTD T on A.PARDRG_CODE = T.AS_DRG_CODE \n");
		sql.append("  left join  DRGS.MR_IPINFO B on A.PARID=b.pmainindex LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=b.PMAININDEX  where T.AS_ID ='"+benchmarkYear+"' AND A.PARDRG_CODE IS NOT NULL and \n");
		sql.append("  B.P00320000 <61  "+Arith.mrAROutDateSql(dateFrom,dateTo,"b")+" AND A.VER_CODE='"+drgVersion+"' AND instr('"+hospCode+"',BA.p00010001)>0 \n");
		sql.append("  ) G  ON E.PMAININDEX = G.PARID where dep_code is not null GROUP BY dep.dep_code,dep.dep_name  \n");
		if("EF".equals(quotaType)){
			sql.append(" order by tim desc,fee desc \n");
		}else if("CW".equals(quotaType)){
			sql.append(" order by cmi desc,totalweight desc \n");
		}
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ;
		return list;
	}
	/**
	 * 全院低风险组死亡病例科室分布
	 */
	@Override
	public List<Map<String, Object>> findDepByRiskAList(Map<String, Object> map) {
		String dateFrom = map == null?"":map.get("dateFrom").toString();
		String dateTo = map == null?"":map.get("dateTo").toString();
		String benchmarkYear = map == null?"":map.get("benchmarkYear").toString();
		String drgVersion = map == null?"":map.get("drgVersion").toString();
		String hospCode = map == null?"":map.get("hospCode").toString(); //获得院区信息
		StringBuffer sql= new StringBuffer();
		sql.append(" SELECT DG.dep DEP, DG.incnt INC, DG.deadcnt DCN,DG.ratio RAT FROM (  \n");
		sql.append(" SELECT a.P00300000 dep ,sum(case WHEN c.AS_RISK_LEVEL='1' then 1 else 0 end)  incnt,  \n");
		sql.append(" sum(case WHEN c.AS_RISK_LEVEL='1' AND a.P00620001='5' then 1 else 0 end)  deadcnt, \n");
		sql.append(" ROUND(decode(sum(case WHEN c.AS_RISK_LEVEL='1' then 1 else 0 end),0,0,sum(case WHEN c.AS_RISK_LEVEL='1'AND a.P00620001='5' then 1 else 0 end)/sum(case WHEN c.AS_RISK_LEVEL='1' then 1 else 0 end)),4)*100 as ratio \n");
		sql.append(" from drgs.MR_IPINFO a LEFT JOIN drgs.t_patientdrg b  ON a.PMAININDEX=b.PARID and b.ver_code='"+drgVersion+"' \n");
		sql.append(" LEFT JOIN drgs.t_annualstd c on b.PARDRG_CODE=c.AS_DRG_CODE LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=A.PMAININDEX  \n");
		sql.append(" WHERE instr('"+hospCode+"',BA.p00010001)>0 AND a.P00320000 <61 and b.PARDRG_CODE is not NULL  \n");
		sql.append(" and c.AS_ID= '"+benchmarkYear+"' AND 1=1 "+Arith.mrAROutDateSql(dateFrom,dateTo,"a")+"   \n");
		sql.append(" GROUP BY a.P00300000  ORDER BY deadcnt DESC   \n");
		sql.append(" )DG WHERE DG.incnt <>0 AND DG.ratio >0  \n");
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ;
		return list;
	}
	/**
	 *全院中低风险组死亡病例科室分布
	 */
	@Override
	public List<Map<String, Object>> findDepByRiskBList(Map<String, Object> map) {
		String dateFrom = map == null?"":map.get("dateFrom").toString();
		String dateTo = map == null?"":map.get("dateTo").toString();
		String benchmarkYear = map == null?"":map.get("benchmarkYear").toString();
		String drgVersion = map == null?"":map.get("drgVersion").toString();
		String hospCode = map == null?"":map.get("hospCode").toString(); //获得院区信息
		StringBuffer sql= new StringBuffer();
		sql.append(" SELECT DG.dep DEP, DG.incnt INC, DG.deadcnt DCN,DG.ratio RAT FROM (  \n");
		sql.append(" SELECT a.P00300000 dep ,sum(case WHEN c.AS_RISK_LEVEL='2' then 1 else 0 end)  incnt,  \n");
		sql.append(" sum(case WHEN c.AS_RISK_LEVEL='2' AND a.P00620001='5' then 1 else 0 end)  deadcnt, \n");
		sql.append(" ROUND(decode(sum(case WHEN c.AS_RISK_LEVEL='2' then 1 else 0 end),0,0,sum(case WHEN c.AS_RISK_LEVEL='2'AND a.P00620001='5' then 1 else 0 end)/sum(case WHEN c.AS_RISK_LEVEL='2' then 1 else 0 end)),4)*100 as ratio \n");
		sql.append(" from drgs.MR_IPINFO a LEFT JOIN drgs.t_patientdrg b  ON a.PMAININDEX=b.PARID and b.ver_code='"+drgVersion+"' \n");
		sql.append(" LEFT JOIN drgs.t_annualstd c on b.PARDRG_CODE=c.AS_DRG_CODE LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=A.PMAININDEX  \n");
		sql.append(" WHERE instr('"+hospCode+"',BA.p00010001)>0 AND a.P00320000 <61 and b.PARDRG_CODE is not NULL  \n");
		sql.append(" and c.AS_ID= '"+benchmarkYear+"' AND 1=1 "+Arith.mrAROutDateSql(dateFrom,dateTo,"a")+"  \n");
		sql.append(" GROUP BY a.P00300000  ORDER BY deadcnt DESC   \n");
		sql.append(" )DG WHERE DG.incnt <>0 AND DG.ratio >0  \n");
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ;
		return list;
	}
	/**
	 *全院MDC覆盖率前五位专业及覆盖率
	 */
	@Override
	public List<Map<String, Object>> findMDCRatioList(Map<String, Object> map) {
		String dateFrom = map == null?"":map.get("dateFrom").toString();
		String dateTo = map == null?"":map.get("dateTo").toString();
		String drgVersion = map == null?"":map.get("drgVersion").toString();
		String hospCode = map == null?"":map.get("hospCode").toString(); //获得院区信息
		StringBuffer sql= new StringBuffer();
		sql.append(" select mdc,mdcDesc,round(pd.mdccnt/mdc.cnt*100,2) drgRatio from (  \n");
		sql.append(" select a.mdc,mdc.mdc_desc,count(distinct a.pardrg_code)mdcCnt from drgs.t_patientdrg a \n");
		sql.append(" left join drgs.mr_ipinfo b on a.parid=b.pmainindex LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=b.PMAININDEX \n");
		sql.append(" left join drgs.t_mdcgroup mdc on mdc.mdc_code=a.mdc and mdc.ver_code='"+drgVersion+"' \n");
		sql.append(" where instr('"+hospCode+"',BA.p00010001)>0 AND B.P00320000 <61  "+Arith.mrAROutDateSql(dateFrom,dateTo,"b")+" group by a.mdc,mdc.mdc_desc)pd left join ( \n");
		sql.append(" select mdc.mdc_code,mdc.mdc_desc mdcDesc,count(*) cnt from drgs.t_drgs drg \n");
		sql.append(" left join drgs.t_adrg adrg on drg.adrg_code=adrg.adrg_code and drg.ver_code=adrg.ver_code \n");
		sql.append(" left join drgs.t_mdcgroup mdc on mdc.mdc_code=adrg.mdc_code and mdc.ver_code=adrg.ver_code \n");
		sql.append(" where drg.ver_code='"+drgVersion+"' group by mdc.mdc_code,mdc.mdc_desc \n");
		sql.append(" )mdc on mdc.mdc_code=pd.mdc where pd.mdc is not null order by drgratio desc \n");
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ;
		return list;
	}
	/**
	 * DRG低风险组死亡病例科室分布
	 */
	@Override
	public List<Map<String, Object>> findDRGByRiskAList(Map<String, Object> map) {
		String dateFrom = map == null?"":map.get("dateFrom").toString();
		String dateTo = map == null?"":map.get("dateTo").toString();
		String benchmarkYear = map == null?"":map.get("benchmarkYear").toString();
		String drgVersion = map == null?"":map.get("drgVersion").toString();
		String hospCode = map == null?"":map.get("hospCode").toString(); //获得院区信息
		StringBuffer sql= new StringBuffer();
		sql.append(" SELECT DG.DRG DRG, DG.incnt INC, DG.deadcnt DCN,DG.ratio RAT FROM (  \n");
		sql.append(" SELECT B.PARDRG_CODE||'-'||m.drg_desc DRG ,sum(case WHEN c.AS_RISK_LEVEL='1' then 1 else 0 end)  incnt,  \n");
		sql.append(" sum(case WHEN c.AS_RISK_LEVEL='1' AND a.P00620001='5' then 1 else 0 end)  deadcnt, \n");
		sql.append(" ROUND(decode(sum(case WHEN c.AS_RISK_LEVEL='1' then 1 else 0 end),0,0,sum(case WHEN c.AS_RISK_LEVEL='1'AND a.P00620001='5' then 1 else 0 end)/sum(case WHEN c.AS_RISK_LEVEL='1' then 1 else 0 end)),4)*100 as ratio \n");
		sql.append(" from drgs.MR_IPINFO a LEFT JOIN drgs.t_patientdrg b  ON a.PMAININDEX=b.PARID and b.ver_code='"+drgVersion+"' \n");
		sql.append(" LEFT JOIN drgs.t_annualstd c on b.PARDRG_CODE=c.AS_DRG_CODE LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=A.PMAININDEX \n");
		sql.append(" LEFT JOIN DRGS.T_DRGS m on m.drg_code=b.pardrg_code and m.ver_code='"+drgVersion+"'  \n");
		sql.append(" WHERE instr('"+hospCode+"',BA.p00010001)>0 AND a.P00320000 <61 and b.PARDRG_CODE is not NULL  \n");
		sql.append(" and c.AS_id= '"+benchmarkYear+"' AND 1=1 "+Arith.mrAROutDateSql(dateFrom,dateTo,"a")+"   \n");
		sql.append(" GROUP BY B.PARDRG_CODE||'-'||m.drg_desc ORDER BY deadcnt DESC   \n");
		sql.append(" )DG WHERE DG.incnt <>0 AND DG.ratio >0  \n");
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ;
		return list;
	}
	/**
	 *DRG中低风险组死亡病例科室分布
	 */
	@Override
	public List<Map<String, Object>> findDRGByRiskBList(Map<String, Object> map) {
		String dateFrom = map == null?"":map.get("dateFrom").toString();
		String dateTo = map == null?"":map.get("dateTo").toString();
		String benchmarkYear = map == null?"":map.get("benchmarkYear").toString();
		String drgVersion = map == null?"":map.get("drgVersion").toString();
		String hospCode = map == null?"":map.get("hospCode").toString(); //获得院区信息
		StringBuffer sql= new StringBuffer();
		sql.append(" SELECT DG.DRG DRG, DG.incnt INC, DG.deadcnt DCN,DG.ratio RAT FROM (  \n");
		sql.append(" SELECT B.PARDRG_CODE||'-'||m.drg_desc DRG ,sum(case WHEN c.AS_RISK_LEVEL='2' then 1 else 0 end)  incnt,  \n");
		sql.append(" sum(case WHEN c.AS_RISK_LEVEL='2' AND a.P00620001='5' then 1 else 0 end)  deadcnt, \n");
		sql.append(" ROUND(decode(sum(case WHEN c.AS_RISK_LEVEL='2' then 1 else 0 end),0,0,sum(case WHEN c.AS_RISK_LEVEL='2'AND a.P00620001='5' then 1 else 0 end)/sum(case WHEN c.AS_RISK_LEVEL='2' then 1 else 0 end)),4)*100 as ratio \n");
		sql.append(" from drgs.MR_IPINFO a LEFT JOIN drgs.t_patientdrg b  ON a.PMAININDEX=b.PARID and b.ver_code='"+drgVersion+"' \n");
		sql.append(" LEFT JOIN drgs.t_annualstd c on b.PARDRG_CODE=c.AS_DRG_CODE LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=A.PMAININDEX \n");
		sql.append(" LEFT JOIN DRGS.T_DRGS m on m.drg_code=b.pardrg_code and m.ver_code='"+drgVersion+"'  \n");
		sql.append(" WHERE instr('"+hospCode+"',BA.p00010001)>0 AND a.P00320000 <61 and b.PARDRG_CODE is not NULL  \n");
		sql.append(" and c.AS_ID= '"+benchmarkYear+"' AND 1=1 "+Arith.mrAROutDateSql(dateFrom,dateTo,"a")+"  \n");
		sql.append(" GROUP BY B.PARDRG_CODE||'-'||m.drg_desc ORDER BY deadcnt DESC   \n");
		sql.append(" )DG WHERE DG.incnt <>0 AND DG.ratio >0  \n");
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ;
		return list;
	}
	/**
	 * 表格 8  一二级手术例数最多的前十名医师列表
	 */
	@Override
	public List<Map<String, Object>> findDocOperAList(Map<String, Object> map) {
		 String dateFrom = map == null?"":map.get("dateFrom").toString();
		 String dateTo = map == null?"":map.get("dateTo").toString();
		 String drgVersion = map == null?"":map.get("drgVersion").toString();
		 String benchmarkYear = map == null?"":map.get("benchmarkYear").toString();
		 String hospCode = map == null?"":map.get("hospCode").toString(); //获得院区信息
		StringBuilder sql = new StringBuilder();
		sql.append(" select X.fopdc DOC, X.fdep DEP, X.fcnt CNT, X.fscnt FSCNT, X.fraio RAT,X.FCMI,X.FINCNT  from ( \n");
		sql.append(" select a.opdc fopdc  ,a.P00300000 fdep,a.allcnt fcnt,a.sumcnt fscnt,ROUND(a.sumcnt/a.allcnt,4)*100 fraio,b.incnt fincnt, b.cmi fcmi from ( \n");
		sql.append(" SELECT op.PO0400 as opdc ,COUNT(op.PO0400) as allcnt ,mr.P00300000 , \n");
		sql.append(" SUM(CASE WHEN op.PO0301='1' OR op.PO0301='2' THEN 1 else 0 end) as sumcnt  \n");
		sql.append(" from drgs.MR_OPERATION  op  \n");
		sql.append(" LEFT JOIN drgs.MR_IPINFO mr on op.PMAININDEX=mr.PMAININDEX LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=MR.PMAININDEX \n");
		sql.append(" WHERE instr('"+hospCode+"',BA.p00010001)>0 AND op.PO0400 is not null  and op.PO1400='1' and  op.POTYPE='M' \n");
		sql.append("  "+Arith.mrAROutDateSql(dateFrom,dateTo,"mr")+" \n");
		sql.append(" GROUP BY op.PO0400,mr.P00300000   \n");
		sql.append(" )a LEFT JOIN (SELECT   op.PO0400 as opdcb , \n");
		sql.append(" sum(case when pg.PARDRG_CODE is not null THEN 1 else 0 end ) as incnt , \n");
		sql.append(" ROUND(sum(ad.AS_WEIGHT)/COUNT(op.PO0400),2)  as cmi  \n");
		sql.append(" from drgs.MR_OPERATION  op  \n");
	    sql.append(" LEFT JOIN drgs.MR_IPINFO mr on op.PMAININDEX=mr.PMAININDEX LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=MR.PMAININDEX \n");
		sql.append(" LEFT JOIN drgs.t_patientdrg pg    on pg.PARID=mr.PMAININDEX  \n");
		sql.append(" LEFT JOIN drgs.t_annualstd ad     on ad.AS_DRG_CODE=pg.PARDRG_CODE \n");
	    sql.append(" WHERE op.PO0400 is not null and pg.VER_CODE='"+drgVersion+"'  and op.PO1400='1' and  op.POTYPE='M'  and ad.AS_ID='"+benchmarkYear+"' \n");
	    sql.append("  "+Arith.mrAROutDateSql(dateFrom,dateTo,"mr")+" AND instr('"+hospCode+"',BA.p00010001)>0 \n");
	    sql.append(" GROUP BY op.PO0400  )b on a.opdc=b.opdcb  ORDER BY a.sumcnt DESC ,A.ALLCNT DESC )X where rownum<=10  \n");
	    List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ;
		return list;
	}
	/**
	 * 表格 9  三四级手术例数最多的前十名医师列表
	 */
	@Override
	public List<Map<String, Object>> findDocOperBList(Map<String, Object> map) {
		 String dateFrom = map == null?"":map.get("dateFrom").toString();
		 String dateTo = map == null?"":map.get("dateTo").toString();
		 String drgVersion = map == null?"":map.get("drgVersion").toString();
		 String benchmarkYear = map == null?"":map.get("benchmarkYear").toString();
		 String hospCode = map == null?"":map.get("hospCode").toString(); //获得院区信息
		StringBuilder sql = new StringBuilder();
		sql.append(" select X.fopdc DOC, X.fdep DEP, X.fcnt CNT, X.fscnt FSCNT, X.fraio RAT,X.FCMI,X.FINCNT  from ( \n");
		sql.append(" select a.opdc fopdc  ,a.P00300000 fdep,a.allcnt fcnt,a.sumcnt fscnt,ROUND(a.sumcnt/a.allcnt,4)*100 fraio,b.incnt fincnt, b.cmi fcmi from ( \n");
		sql.append(" SELECT op.PO0400 as opdc ,COUNT(op.PO0400) as allcnt ,mr.P00300000 , \n");
		sql.append(" SUM(CASE WHEN op.PO0301='3' OR op.PO0301='4' THEN 1 else 0 end) as sumcnt  \n");
		sql.append(" from drgs.MR_OPERATION  op  \n");
		sql.append(" LEFT JOIN drgs.MR_IPINFO mr on op.PMAININDEX=mr.PMAININDEX LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=MR.PMAININDEX  \n");
		sql.append(" WHERE instr('"+hospCode+"',BA.p00010001)>0 AND op.PO0400 is not null  and op.PO1400='1' and  op.POTYPE='M' \n");
		sql.append("  "+Arith.mrAROutDateSql(dateFrom,dateTo,"mr")+" \n");
		sql.append(" GROUP BY op.PO0400,mr.P00300000   \n");
		sql.append(" )a LEFT JOIN (SELECT   op.PO0400 as opdcb , \n");
		sql.append(" sum(case when pg.PARDRG_CODE is not null THEN 1 else 0 end ) as incnt , \n");
		sql.append(" ROUND(sum(ad.AS_WEIGHT)/COUNT(op.PO0400),2)  as cmi  \n");
		sql.append(" from drgs.MR_OPERATION  op  \n");
	    sql.append(" LEFT JOIN drgs.MR_IPINFO mr on op.PMAININDEX=mr.PMAININDEX LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=MR.PMAININDEX  \n");
		sql.append(" LEFT JOIN drgs.t_patientdrg pg    on pg.PARID=mr.PMAININDEX  \n");
		sql.append(" LEFT JOIN drgs.t_annualstd ad     on ad.AS_DRG_CODE=pg.PARDRG_CODE \n");
	    sql.append(" WHERE op.PO0400 is not null and pg.VER_CODE='"+drgVersion+"'  and op.PO1400='1' and  op.POTYPE='M'  and ad.AS_ID='"+benchmarkYear+"' \n");
	    sql.append("  "+Arith.mrAROutDateSql(dateFrom,dateTo,"mr")+" AND instr('"+hospCode+"',BA.p00010001)>0 \n");
	    sql.append(" GROUP BY op.PO0400  )b on a.opdc=b.opdcb  ORDER BY a.sumcnt DESC,A.ALLCNT DESC )X where rownum<=10  \n");
	    List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ;
		return list;
	}
	/**
	 * 获取重点专科名称及病例数
	 */
	@Override
	public List<Map<String, Object>> findMdcDescAndCnt(Map<String, Object> map) {
		String dateFrom = map == null?"":map.get("dateFrom").toString();
		String dateTo = map == null?"":map.get("dateTo").toString();
		String drgVersion = map == null?"":map.get("drgVersion").toString();
		String mdcCode = map == null ? "" : map.get("mdcCode").toString();
		String hospCode = map == null?"":map.get("hospCode").toString(); //获得院区信息
		StringBuffer sql = new StringBuffer();
		sql.append(" select  b.mdc_code,b.mdc_desc,count(*) mdcCnt from drgs.t_patientdrg a  \n");
		sql.append(" left join drgs.t_mdcgroup b on a.mdc=b.mdc_code and a.VER_CODE=b.VER_CODE  \n");
		sql.append(" left join drgs.mr_ipinfo c on a.parid=c.pmainindex LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=C.PMAININDEX \n");
		sql.append(" where instr('"+hospCode+"',BA.p00010001)>0 AND b.mdc_code in (" + mdcCode + ") and a.ver_code='"+drgVersion+"'  \n");
		sql.append(" "+Arith.mrAROutDateSql(dateFrom,dateTo,"c")+" group by b.mdc_code,b.mdc_desc \n");
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ; 
		return list;
	}
	/**
	 * 获取重点专科理论DRGs组数
	 */
	@Override
	public int findMdcDRGsCnt(Map<String, Object> map) {
		String mdcCode = map == null ? "" : map.get("mdcCode").toString();
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT COUNT(DISTINCT A.DRG_CODE) FROM drgs.t_drgs a WHERE  ('MDC' ||  substr(a.DRG_CODE, 0,1))  in ("
				+ mdcCode
				+ ")  AND a.VER_CODE  in (SELECT DISTINCT b.VER_CODE FROM drgs.t_patientdrg b )  \n");
		Object drgscnt = getRepository().createSqlQuery(sql.toString()).singleResult();
		return Integer.parseInt(drgscnt == null ? "0" : drgscnt + "");
	}
	/**
	 * 获取重点专科实际DRGs组数
	 */
	@Override
	public int findMdcDRGsRealCnt(Map<String, Object> map) {
		String dateFrom = map == null?"":map.get("dateFrom").toString();
		String dateTo = map == null?"":map.get("dateTo").toString();
		String mdcCode = map == null ? "" : map.get("mdcCode").toString();
		String hospCode = map == null?"":map.get("hospCode").toString(); 
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT COUNT(DISTINCT(b.PARDRG_CODE)) FROM drgs.mr_ipinfo a  \n");
		sql.append(" LEFT JOIN drgs.t_patientdrg b on a.pmainindex=b.PARID LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=A.PMAININDEX \n");
		sql.append(" WHERE instr('"+hospCode+"',BA.p00010001)>0 AND b.PARDRG_CODE is not NULL "+Arith.mrAROutDateSql(dateFrom,dateTo,"a")+" \n");
		sql.append(" and b.MDC in (" + mdcCode + ") \n");
		Object casecnt = getRepository().createSqlQuery(sql.toString()).singleResult();
		return Integer.parseInt(casecnt == null ? "0" : casecnt + "");
	}
	/**
	 * 获取重点专科出院病例数排名前十DRGs
	 */
	@Override
	public List<Map<String, Object>> findDRGsTop10CntByMDC(Map<String, Object> map) {
		String dateFrom = map == null?"":map.get("dateFrom").toString();
		String dateTo = map == null?"":map.get("dateTo").toString();
		String drgVersion = map == null?"":map.get("drgVersion").toString();
		String benchmarkYear = map == null?"":map.get("benchmarkYear").toString();
		String mdcCode = map == null ? "" : map.get("mdcCode").toString();
		String hospCode = map == null?"":map.get("hospCode").toString(); 
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT * FROM (SELECT B.PARDRG_CODE,D.DRG_DESC,count(B.PARID) AS CNT,C.AS_WEIGHT FROM drgs.mr_ipinfo A \n");
		sql.append(" LEFT JOIN drgs.t_patientdrg B ON A.Pmainindex=B.PARID LEFT JOIN drgs.t_annualstd C ON B.PARDRG_CODE=C.AS_DRG_CODE \n");
		sql.append(" LEFT JOIN drgs.t_drgs D ON B.PARDRG_CODE=D.DRG_CODE and d.ver_code=b.ver_code LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=A.PMAININDEX \n");
		sql.append(" WHERE instr('"+hospCode+"',BA.p00010001)>0 AND C.AS_ID='"+benchmarkYear+"' AND B.PARDRG_CODE IS NOT NULL and b.ver_code='"+drgVersion+"' \n");
		sql.append(" "+Arith.mrAROutDateSql(dateFrom,dateTo,"a")+" AND  B.VER_CODE=D.VER_CODE AND B.MDC in (" + mdcCode + ") \n");
		sql.append(" GROUP BY B.PARDRG_CODE,D.DRG_DESC,C.AS_WEIGHT  ORDER BY CNT DESC)WHERE ROWNUM<11 \n");
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ; 
		return list;
	}
	/**
	 * 获取重点专科总权重排名前十DRGs
	 */
	@Override
	public List<Map<String, Object>> findDRGsTop10WeightByMDC(Map<String, Object> map) {
		String dateFrom = map == null?"":map.get("dateFrom").toString();
		String dateTo = map == null?"":map.get("dateTo").toString();
		String drgVersion = map == null?"":map.get("drgVersion").toString();
		String benchmarkYear = map == null?"":map.get("benchmarkYear").toString();
		String mdcCode = map == null ? "" : map.get("mdcCode").toString();
		String hospCode = map == null?"":map.get("hospCode").toString(); 
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT B.PARDRG_CODE,D.DRG_DESC,count(B.PARID) AS CNT,C.AS_WEIGHT,count(B.PARID)*AS_WEIGHT totalWeight FROM drgs.mr_ipinfo A \n");
		sql.append(" LEFT JOIN drgs.t_patientdrg B ON A.Pmainindex=B.PARID LEFT JOIN drgs.t_annualstd C ON B.PARDRG_CODE=C.AS_DRG_CODE \n");
		sql.append(" LEFT JOIN drgs.t_drgs D ON B.PARDRG_CODE=D.DRG_CODE and d.ver_code=b.ver_code LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=A.PMAININDEX \n");
		sql.append(" WHERE instr('"+hospCode+"',BA.p00010001)>0 AND C.AS_id='"+benchmarkYear+"' AND B.PARDRG_CODE IS NOT NULL and b.ver_code='"+drgVersion+"' \n");
		sql.append(" "+Arith.mrAROutDateSql(dateFrom,dateTo,"a")+" AND  B.VER_CODE=D.VER_CODE AND B.MDC in (" + mdcCode + ") \n");
		sql.append(" GROUP BY B.PARDRG_CODE,D.DRG_DESC,C.AS_WEIGHT  ORDER BY totalWeight DESC \n");
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ; 
		return list;
	}
	/**
	 * 获取医管局考核病组名称
	 */
	@Override
	public List<Map<String, Object>> findDRGSDesc(Map<String, Object> map) {
		String drgVersion = map == null?"":map.get("drgVersion").toString();
		String drgCodes = map == null ? "" : map.get("drgCodes").toString();
		StringBuffer sql = new StringBuffer();
		sql.append(" select a.drg_code,a.drg_desc from drgs.t_drgs a  \n");
		sql.append(" where a.ver_code='"+drgVersion+"' and a.drg_code in ("+drgCodes+") \n");
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ; 
		return list;
	}
	/**
	 * 获取分组器版本信息
	 */
	@Override
	public String findVersionDesc(Map<String, Object> map) {
		String drgVersion = map == null?"":map.get("drgVersion").toString();
		String sqlResult = " select t.ver_desc from drgs.T_DRGVERSION t where t.ver_code='"+drgVersion+"' ";
		String versionDesc = jdbcTemplateObj.queryForObject(sqlResult.toString(), String.class);
		return versionDesc;
	}
	/**
	 * 表格12 获取医管局考核病组数据
	 */
	@Override
	public List<Map<String, Object>> findDRGsQuotaList(Map<String, Object> map,String dateFrom,String dateTo,String drgCode) {
		String drgVersion = map == null?"":map.get("drgVersion").toString();
		String benchmarkYear = map == null?"":map.get("benchmarkYear").toString();
		String hospCode = map == null?"":map.get("hospCode").toString(); 
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT B.PARDRG_CODE||'-'||D.DRG_DESC DRGDESC,count(B.PARID) AS CNT,C.AS_WEIGHT,count(B.PARID)*AS_WEIGHT totalWeight , \n");
		sql.append(" round(decode(COUNT(b.parid),0,0,sum(decode(c.AS_AVG_FEE,0,0,a.P00800000/c.AS_AVG_FEE))/COUNT(b.parid)),2)  feeeff, \n");
		sql.append(" round(decode(COUNT(b.parid),0,0,sum(decode(c.AS_AVG_DAYS,0,0,a.P00320000/c.AS_AVG_DAYS))/COUNT(b.parid)),2) timeeff \n");
		sql.append(" FROM drgs.mr_ipinfo A LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=A.PMAININDEX LEFT JOIN drgs.t_patientdrg B ON A.Pmainindex=B.PARID LEFT JOIN drgs.t_annualstd C ON B.PARDRG_CODE=C.AS_DRG_CODE \n");
		sql.append(" LEFT JOIN drgs.t_drgs D ON B.PARDRG_CODE=D.DRG_CODE and d.ver_code=b.ver_code \n");
		sql.append(" WHERE instr('"+hospCode+"',BA.p00010001)>0 AND C.AS_ID='"+benchmarkYear+"' AND B.PARDRG_CODE IS NOT NULL and b.ver_code='"+drgVersion+"' \n");
		sql.append(" "+Arith.mrAROutDateSql(dateFrom,dateTo,"a")+" AND  B.VER_CODE=D.VER_CODE AND B.PARDRG_CODE='"+drgCode+"' \n");
		sql.append(" GROUP BY B.PARDRG_CODE,D.DRG_DESC,C.AS_WEIGHT  ORDER BY totalWeight DESC \n");
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ; 
		return list;
	}
	/**
	 * 表格13 获取重点专业缺失DRG
	 */
	public List<Map<String, Object>> findLostDRGsList(Map<String, Object> map){
		String dateFrom = map == null?"":map.get("dateFrom").toString();
		String dateTo = map == null?"":map.get("dateTo").toString();
		String drgVersion = map == null?"":map.get("drgVersion").toString();
		String benchmarkYear = map == null?"":map.get("benchmarkYear").toString();
		String mdcCode = map == null ? "" : map.get("mdcCode").toString();
		String hospCode = map == null?"":map.get("hospCode").toString(); 
		StringBuffer sql = new StringBuffer();
		sql.append(" select * from (SELECT DRG_CODE,DRG_DESC,std.as_weight FROM drgs.t_drgs drg \n");
		sql.append(" left join drgs.t_adrg adrg on drg.adrg_code=adrg.adrg_code and drg.ver_code=adrg.ver_code \n");
		sql.append(" left join drgs.t_mdcgroup mdc on mdc.mdc_code=adrg.mdc_code and mdc.ver_code=adrg.ver_code \n");
		sql.append(" left join drgs.t_annualstd std on std.as_ID='"+benchmarkYear+"' and std.as_drg_code=drg.drg_code \n");
		sql.append(" where drg.ver_code='"+drgVersion+"' and mdc.mdc_code in (" + mdcCode + "))a \n");
		sql.append(" where a.DRG_CODE not in(select distinct pd.pardrg_code from drgs.t_patientdrg pd \n");
		sql.append(" left join drgs.mr_ipinfo b on pd.parid=b.pmainindex LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=b.PMAININDEX \n");
		sql.append(" where instr('"+hospCode+"',BA.p00010001)>0 AND pd.mdc in (" + mdcCode + ") and pd.ver_code='"+drgVersion+"' "+Arith.mrAROutDateSql(dateFrom,dateTo,"b")+") \n");
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ; 
		return list;
	};
	/**
	 * 获取时间消耗指数费用消耗指数大于1的个数
	 * @param map
	 * @return
	 */
	public List<Map<String, Object>> findoverTimeEffAndFeeEffList(Map<String, Object> map){
		String dateFrom = map == null?"":map.get("dateFrom").toString();
		String dateTo = map == null?"":map.get("dateTo").toString();
		String drgVersion = map == null?"":map.get("drgVersion").toString();
		String benchmarkYear = map == null?"":map.get("benchmarkYear").toString();
		String hospCode = map == null?"":map.get("hospCode").toString(); 
		StringBuffer sql = new StringBuffer();
		sql.append(" select count(*) cnt ,sum(case when feeeff>1 then 1 else 0 end)feeeffover,sum(case when timeeff>1 then 1 else 0 end)timeeffover \n");
		sql.append(" from (SELECT B.PARDRG_CODE||'('||D.DRG_DESC||')' drg, \n");
		sql.append(" round(decode(COUNT(b.parid),0,0,sum(decode(c.AS_AVG_FEE,0,0,a.P00800000/c.AS_AVG_FEE))/COUNT(b.parid)),2)  feeeff, \n");
		sql.append(" round(decode(COUNT(b.parid),0,0,sum(decode(c.AS_AVG_DAYS,0,0,a.P00320000/c.AS_AVG_DAYS))/COUNT(b.parid)),2) timeeff \n");
		sql.append(" FROM drgs.mr_ipinfo A LEFT JOIN drgs.t_patientdrg B ON A.Pmainindex=B.PARID LEFT JOIN drgs.t_annualstd C ON B.PARDRG_CODE=C.AS_DRG_CODE \n");
		sql.append(" LEFT JOIN drgs.t_drgs D ON B.PARDRG_CODE=D.DRG_CODE and d.ver_code=b.ver_code LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=A.PMAININDEX\n");
		sql.append(" WHERE instr('"+hospCode+"',BA.p00010001)>0 AND C.AS_ID='"+benchmarkYear+"' AND B.PARDRG_CODE IS NOT NULL and b.ver_code='"+drgVersion+"' \n");
		sql.append(" "+Arith.mrAROutDateSql(dateFrom,dateTo,"a")+" AND  B.VER_CODE=D.VER_CODE GROUP BY B.PARDRG_CODE,D.DRG_DESC) \n");
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ; 
		return list;
	}
	/**
	 * 获取时间消耗指数前五的DRG
	 * @param map
	 * @return
	 */
	public List<Map<String, Object>> findoverFiveTimeEffList(Map<String, Object> map){
		String dateFrom = map == null?"":map.get("dateFrom").toString();
		String dateTo = map == null?"":map.get("dateTo").toString();
		String drgVersion = map == null?"":map.get("drgVersion").toString();
		String benchmarkYear = map == null?"":map.get("benchmarkYear").toString();
		String hospCode = map == null?"":map.get("hospCode").toString(); 
		StringBuffer sql = new StringBuffer();
		sql.append(" select * from (SELECT B.PARDRG_CODE||'('||D.DRG_DESC||')' drg, \n");
		sql.append(" round(decode(COUNT(b.parid),0,0,sum(decode(c.AS_AVG_DAYS,0,0,a.P00320000/c.AS_AVG_DAYS))/COUNT(b.parid)),2) timeeff  \n");
		sql.append(" FROM drgs.mr_ipinfo A LEFT JOIN drgs.t_patientdrg B ON A.Pmainindex=B.PARID LEFT JOIN drgs.t_annualstd C ON B.PARDRG_CODE=C.AS_DRG_CODE \n");
		sql.append(" LEFT JOIN drgs.t_drgs D ON B.PARDRG_CODE=D.DRG_CODE and d.ver_code=b.ver_code LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=A.PMAININDEX \n");
		sql.append(" WHERE instr('"+hospCode+"',BA.p00010001)>0 AND C.AS_ID='"+benchmarkYear+"' AND B.PARDRG_CODE IS NOT NULL and b.ver_code='"+drgVersion+"' \n");
		sql.append(" "+Arith.mrAROutDateSql(dateFrom,dateTo,"a")+" AND  B.VER_CODE=D.VER_CODE GROUP BY B.PARDRG_CODE,D.DRG_DESC order by timeeff desc) where rownum<6 \n");
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ; 
		return list;
	}
	/**
	 * 获取费用消耗指数前五的DRG
	 * @param map
	 * @return
	 */
	public List<Map<String, Object>> findoverFiveFeeEffList(Map<String, Object> map){
		String dateFrom = map == null?"":map.get("dateFrom").toString();
		String dateTo = map == null?"":map.get("dateTo").toString();
		String drgVersion = map == null?"":map.get("drgVersion").toString();
		String benchmarkYear = map == null?"":map.get("benchmarkYear").toString();
		String hospCode = map == null?"":map.get("hospCode").toString(); 
		StringBuffer sql = new StringBuffer();
		sql.append(" select * from (SELECT B.PARDRG_CODE||'('||D.DRG_DESC||')' drg, \n");
		sql.append(" round(decode(COUNT(b.parid),0,0,sum(decode(c.AS_AVG_FEE,0,0,a.P00800000/c.AS_AVG_FEE))/COUNT(b.parid)),2)  feeeff \n");
		sql.append(" FROM drgs.mr_ipinfo A LEFT JOIN drgs.t_patientdrg B ON A.Pmainindex=B.PARID LEFT JOIN drgs.t_annualstd C ON B.PARDRG_CODE=C.AS_DRG_CODE \n");
		sql.append(" LEFT JOIN drgs.t_drgs D ON B.PARDRG_CODE=D.DRG_CODE and d.ver_code=b.ver_code LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=A.PMAININDEX \n");
		sql.append(" WHERE instr('"+hospCode+"',BA.p00010001)>0 AND C.AS_ID='"+benchmarkYear+"' AND B.PARDRG_CODE IS NOT NULL and b.ver_code='"+drgVersion+"' \n");
		sql.append(" "+Arith.mrAROutDateSql(dateFrom,dateTo,"a")+" AND  B.VER_CODE=D.VER_CODE GROUP BY B.PARDRG_CODE,D.DRG_DESC order by feeeff desc) where rownum<6 \n");
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ; 
		return list;
	}
	/**
	 * 获取科室费用消耗指数差值最大的两个DRGs
	 * @param map
	 * @return
	 */
	public List<Map<String, Object>> findMaxDFeeEffDRGList(Map<String, Object> map){
		String dateFrom = map == null?"":map.get("dateFrom").toString();
		String dateTo = map == null?"":map.get("dateTo").toString();
		String drgVersion = map == null?"":map.get("drgVersion").toString();
		String benchmarkYear = map == null?"":map.get("benchmarkYear").toString();
		String hospCode = map == null?"":map.get("hospCode").toString(); 
		StringBuffer sql = new StringBuffer();
		sql.append(" select * from (select drgcode,drgdesc,max(feeeff),min(feeeff),max(feeeff)-min(feeeff)dfeeeff,sum(cnt) cnt from \n");
		sql.append(" (SELECT b.pardrg_code drgcode,d.drg_desc drgdesc,a.p00300000,COUNT(*)CNT,round(avg(P00800000),2)avgfee, \n");
		sql.append(" round(decode(COUNT(b.parid),0,0,sum(decode(c.AS_AVG_FEE,0,0,a.P00800000/c.AS_AVG_FEE))/COUNT(b.parid)),2) feeeff \n");
		sql.append(" FROM drgs.mr_ipinfo A LEFT JOIN drgs.t_patientdrg B ON A.Pmainindex=B.PARID LEFT JOIN drgs.t_annualstd C ON B.PARDRG_CODE=C.AS_DRG_CODE \n");
		sql.append(" LEFT JOIN drgs.t_drgs D ON B.PARDRG_CODE=D.DRG_CODE and d.ver_code=b.ver_code LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=A.PMAININDEX \n");
		sql.append(" WHERE instr('"+hospCode+"',BA.p00010001)>0 AND C.AS_ID='"+benchmarkYear+"' AND B.PARDRG_CODE in (select drg from (SELECT B.PARDRG_CODE drg,COUNT(*)CNT \n");
		sql.append(" FROM drgs.mr_ipinfo A LEFT JOIN drgs.t_patientdrg B ON A.Pmainindex=B.PARID LEFT JOIN drgs.t_annualstd C ON B.PARDRG_CODE=C.AS_DRG_CODE \n");
		sql.append(" LEFT JOIN drgs.t_drgs D ON B.PARDRG_CODE=D.DRG_CODE and d.ver_code=b.ver_code LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=A.PMAININDEX \n");
		sql.append(" WHERE instr('"+hospCode+"',BA.p00010001)>0 AND C.AS_ID='"+benchmarkYear+"' AND B.PARDRG_CODE IS NOT NULL and b.ver_code='"+drgVersion+"' \n");
		sql.append(" "+Arith.mrAROutDateSql(dateFrom,dateTo,"a")+"  AND  B.VER_CODE=D.VER_CODE \n");
		sql.append(" GROUP BY B.PARDRG_CODE,D.DRG_DESC order by CNT desc)where rownum<6) and b.ver_code='"+drgVersion+"' \n");
		sql.append(" "+Arith.mrAROutDateSql(dateFrom,dateTo,"a")+"  AND  B.VER_CODE=D.VER_CODE \n");
		sql.append(" GROUP BY b.pardrg_code,d.drg_desc,a.p00300000 order by pardrg_code,CNT desc) group by drgcode,drgdesc order by dfeeeff desc)where rownum<3 \n");
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ; 
		return list;
	}
	/**
	 * 获取科室费用消耗指数差值最大的两个DRGs的科室次均费用
	 * @param map
	 * @return
	 */
	public List<Map<String, Object>> findMaxDFeeEffDRGDepList(Map<String, Object> map,String drgCode){
		String dateFrom = map == null?"":map.get("dateFrom").toString();
		String dateTo = map == null?"":map.get("dateTo").toString();
		String drgVersion = map == null?"":map.get("drgVersion").toString();
		String benchmarkYear = map == null?"":map.get("benchmarkYear").toString();
		String hospCode = map == null?"":map.get("hospCode").toString(); 
		StringBuffer sql = new StringBuffer();
		sql.append(" select A.P00300000 dep,ROUND(AVG(A.P00800000),2)AVGFEE,COUNT(*) depcnt,avg(std.as_avg_fee) stdFee from drgs.mr_ipinfo a  \n");
		sql.append(" left join drgs.t_patientdrg b on a.pmainindex=b.parid and b.ver_code='"+drgVersion+"' LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=A.PMAININDEX \n");
		sql.append(" left join drgs.t_annualstd std on std.as_drg_code=b.pardrg_code and std.as_ID='"+benchmarkYear+"' \n");
		sql.append(" where instr('"+hospCode+"',BA.p00010001)>0 AND b.pardrg_code='"+drgCode+"' "+Arith.mrAROutDateSql(dateFrom,dateTo,"a")+" GROUP BY A.P00300000 ORDER BY AVGFEE\n");
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ; 
		return list;
	}
	/**
	 * 附表1:全院医疗服务综合能力情况表
	 */
	@Override
	public List<Map<String, Object>> findHospAbilityList(Map<String, Object> map) {
		String dateFrom = map == null?"":map.get("dateFrom").toString();
		String dateTo = map == null?"":map.get("dateTo").toString();
		String drgVersion = map == null?"":map.get("drgVersion").toString();
		String benchmarkYear = map == null?"":map.get("benchmarkYear").toString();
		String hospCode = map == null?"":map.get("hospCode").toString(); 
		StringBuffer sql=new StringBuffer();
		sql.append(" SELECT COUNT(*)TOTALCASECNT,COUNT(p.PARID) INCASECNT ,ROUND(decode(COUNT(P.PARID),0,0,SUM(A .AS_WEIGHT) / COUNT(P.PARID)), 2 )  CMI, \n");
		sql.append(" COUNT(DISTINCT P.PARDRG_CODE) DRGS ,ROUND(SUM(A.AS_WEIGHT),2) WEIGHT,  round(decode(COUNT(P.PARID),0,0,SUM(decode(A.AS_AVG_DAYS,0,0,M.P00320000/A.AS_AVG_DAYS))/COUNT(P.PARID)),2)  TIME, \n");
		sql.append(" round(decode(COUNT(P.PARID),0,0,SUM(decode(A.AS_AVG_FEE,0,0,M.P00800000/A.AS_AVG_FEE))/COUNT(P.PARID)),2)  FEE , \n");
		sql.append(" ROUND(decode(sum(case WHEN A.AS_RISK_LEVEL='1' then 1 else 0 end),0,0,sum(case WHEN A.AS_RISK_LEVEL='1'AND M.P00620001='5' then 1 else 0 end)/sum(case WHEN A.AS_RISK_LEVEL='1' then 1 else 0 end)),4)*100 ARATIO, \n");
		sql.append(" ROUND(decode(sum(case WHEN A.AS_RISK_LEVEL='2' then 1 else 0 end),0,0,sum(case WHEN A.AS_RISK_LEVEL='2'AND M.P00620001='5' then 1 else 0 end)/sum(case WHEN A.AS_RISK_LEVEL='2' then 1 else 0 end)),4)*100 BRATIO, \n");
		sql.append(" ROUND(decode(sum(case WHEN A.AS_RISK_LEVEL='3' then 1 else 0 end),0,0,sum(case WHEN A.AS_RISK_LEVEL='3'AND M.P00620001='5' then 1 else 0 end)/sum(case WHEN A.AS_RISK_LEVEL='3' then 1 else 0 end)),4)*100 CRATIO, \n");
		sql.append(" ROUND(decode(sum(case WHEN A.AS_RISK_LEVEL='4' then 1 else 0 end),0,0,sum(case WHEN A.AS_RISK_LEVEL='4'AND M.P00620001='5' then 1 else 0 end)/sum(case WHEN A.AS_RISK_LEVEL='4' then 1 else 0 end)),4)*100 DRATIO \n");
		sql.append(" FROM DRGS.MR_IPINFO M LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=m.PMAININDEX\n");
		sql.append(" LEFT JOIN DRGS.T_PATIENTDRG P ON M .PMAININDEX = P.PARID and p.ver_code='"+drgVersion+"' and P.PARDRG_CODE is not null \n");
		sql.append(" LEFT JOIN DRGS.T_ANNUALSTD A  ON P.PARDRG_CODE = A.AS_DRG_CODE AND A.AS_ID ='"+benchmarkYear+"'  AND A.AS_WEIGHT IS not NULL  \n");
		sql.append(" WHERE instr('"+hospCode+"',BA.p00010001)>0 "+Arith.mrAROutDateSql(dateFrom,dateTo,"M")+" \n");
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ; 
		return list;
	}
	/**
	 *附表1:全院各科室医疗服务综合能力情况表
	 */
	@Override
	public List<Map<String, Object>> findAllDepAbilityList(Map<String, Object> map,String dateFrom,String dateTo) {
		String drgVersion = map == null?"":map.get("drgVersion").toString();
		String benchmarkYear = map == null?"":map.get("benchmarkYear").toString();
		String hospCode = map == null?"":map.get("hospCode").toString(); 
		StringBuffer sql=new StringBuffer();
		sql.append(" select a.*,case when b.avgWeight<>0 and b.avgDrgCnt<>0 and b.cmi<>0 \n");
		sql.append(" then round(sqrt((a.DRGCount/b.avgDrgCnt)*(a.TotalWeight/b.avgWeight)*(a.cmi/b.cmi)),2) \n");
	    sql.append(" else 0 end  +case when timeeff<>0 and feeeff<>0 then round(sqrt((1/timeeff)*(1/feeeff)),2) else 0 end + \n"); 
		sql.append(" round(sqrt((case when risk1Ratio=0 then 1  when risk1Ratio>0 and risk1Ratio<=0.05 then 0.9 \n"); 
		sql.append(" when risk1Ratio>0.05 and risk1Ratio<=0.1 then 0.8 when risk1Ratio>0.1 and risk1Ratio<=0.15 then 0.7 \n"); 
		sql.append(" when risk1Ratio>0.15 and risk1Ratio<=0.2 then 0.6 when risk1Ratio>0.2 and risk1Ratio<=0.25 then 0.5  \n");
		sql.append(" when risk1Ratio>0.25 and risk1Ratio<=0.3 then 0.4 when risk1Ratio>0.3 and risk1Ratio<=0.35 then 0.3 \n");
		sql.append(" when risk1Ratio>0.35 and risk1Ratio<=0.4 then 0.2 when risk1Ratio>0.4 and risk1Ratio<=0.45 then 0.1 \n");
		sql.append(" else 0 end) * (case when risk2Ratio=0 then 1 when risk2Ratio>0 and risk2Ratio<=0.25 then 0.9 \n");
		sql.append(" when risk2Ratio>0.25 and risk2Ratio<=0.5 then 0.8 when risk2Ratio>0.5 and risk2Ratio<=0.75 then 0.7 \n");
		sql.append(" when risk2Ratio>0.75 and risk2Ratio<=1 then 0.6 when risk2Ratio>0.1 and risk2Ratio<=1.25 then 0.5 \n");
		sql.append(" when risk2Ratio>1.25 and risk2Ratio<=1.5 then 0.4 when risk2Ratio>1.5 and risk2Ratio<=1.75 then 0.3 \n");
		sql.append(" when risk2Ratio>1.75 and risk2Ratio<=2 then 0.2 when risk2Ratio>2 and risk2Ratio<=2.25 then 0.1  \n");
		sql.append(" else 0 end )),2) Score   from (SELECT decode(dep.is_ope_dep,'1','外科','0','内科','其他') dep_type, dep.pardepgroup_code codeupper, \n");
	    sql.append(" M.P00300000 DECC,count(*) totalcasecnt,COUNT(p.PARID) InCaseCnt,ROUND(decode(COUNT(P.PARID),0,0,SUM(A .AS_WEIGHT) / COUNT(P.PARID)), 2 ) CMI, \n"); 
		sql.append(" COUNT(DISTINCT P.PARDRG_CODE) DRGCount ,ROUND(SUM(A.AS_WEIGHT),2) TotalWeight, \n"); 
		sql.append(" round(decode(COUNT(P.PARID),0,0,SUM(decode(A.AS_AVG_DAYS,0,0,M.P00320000/A.AS_AVG_DAYS))/COUNT(P.PARID)),2) timeeff, \n"); 
		sql.append(" round(decode(COUNT(P.PARID),0,0,SUM(decode(A.AS_AVG_FEE,0,0,M.P00800000/A.AS_AVG_FEE))/COUNT(P.PARID)),2) feeeff,  \n");
		sql.append(" ROUND(decode(sum(case WHEN A.AS_RISK_LEVEL='1' then 1 else 0 end),0,0,sum(case WHEN A.AS_RISK_LEVEL='1'AND M.P00620001='5' then 1 else 0 end)/sum(case WHEN A.AS_RISK_LEVEL='1' then 1 else 0 end)),4)*100 risk1Ratio,  \n");
		sql.append(" ROUND(decode(sum(case WHEN A.AS_RISK_LEVEL='2' then 1 else 0 end),0,0,sum(case WHEN A.AS_RISK_LEVEL='2'AND M.P00620001='5' then 1 else 0 end)/sum(case WHEN A.AS_RISK_LEVEL='2' then 1 else 0 end)),4)*100 risk2Ratio, \n");
		sql.append(" ROUND(decode(sum(case WHEN A.AS_RISK_LEVEL='3' then 1 else 0 end),0,0,sum(case WHEN A.AS_RISK_LEVEL='3'AND M.P00620001='5' then 1 else 0 end)/sum(case WHEN A.AS_RISK_LEVEL='3' then 1 else 0 end)),4)*100 risk3Ratio,  \n");
		sql.append(" ROUND(decode(sum(case WHEN A.AS_RISK_LEVEL='4' then 1 else 0 end),0,0,sum(case WHEN A.AS_RISK_LEVEL='4'AND M.P00620001='5' then 1 else 0 end)/sum(case WHEN A.AS_RISK_LEVEL='4' then 1 else 0 end)),4)*100 risk4Ratio \n");
		sql.append(" FROM DRGS.MR_IPINFO M LEFT JOIN DRGS.T_PATIENTDRG P ON M.PMAININDEX = P.PARID and p.ver_code='"+drgVersion+"' and P.PARDRG_CODE is not null \n");
		sql.append(" LEFT JOIN DRGS.T_ANNUALSTD A  ON P.PARDRG_CODE = A.AS_DRG_CODE and A.AS_ID ='"+benchmarkYear+"'  AND A.AS_WEIGHT  IS not NULL \n");
		sql.append(" LEFT JOIN DRGS.T_DEPARTMENT DEP ON DEP.DEP_CODE = M.P00300001 LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=m.PMAININDEX \n");
		sql.append(" WHERE instr('"+hospCode+"',BA.p00010001)>0 "+Arith.mrAROutDateSql(dateFrom,dateTo,"M")+" and dep_type is not null \n");
		sql.append(" GROUP BY dep.is_ope_dep,m.P00300000,dep.pardepgroup_code ORDER BY dep_type DESC,incasecnt desc )a left join \n");
		sql.append(" (select  DHC.codeupper,case when dhc.cnt<>0 then dhc.totalWeight/dhc.cnt else 0 end  as avgWeight, \n");
		sql.append(" case when dhc.cnt<>0 then sum(dhc.drgCnt)/dhc.cnt else 0 end  as avgDrgCnt, \n");
		sql.append(" case when dhc.totalCseCnt<>0  then dhc.totalWeight/dhc.totalCseCnt else 0 end  as cmi from (  \n");
		sql.append(" select distinct depgroup.depgroup_code as codeupper,sum(c.as_weight) over(partition by depgroup.depgroup_code) as totalWeight, \n");
		sql.append(" count(a.pmainindex)over(partition by depgroup.depgroup_code) as totalCseCnt ,count(distinct b.pardrg_code) over(partition by dep.dep_code) drgCnt , \n");
		sql.append(" count(distinct dep.dep_code) over(partition by depgroup.depgroup_code) AS cnt from DRGs.Mr_Baseinfo a  \n");
		sql.append(" left join drgs.mr_ipinfo ipinfo on a.pmainindex = ipinfo.pmainindex LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=ipinfo.PMAININDEX left join DRGs.t_patientdrg b on a.pmainindex=b.parid \n");
		sql.append(" left join drgs.t_department dep on ipinfo.p00300001=dep.dep_code left join drgs.t_depgroup depgroup on depgroup.depgroup_code=dep.pardepgroup_code \n");
		sql.append(" left join DRGs.t_annualstd c on b.pardrg_code=c.as_drg_code and c.AS_ID='"+benchmarkYear+"' where instr('"+hospCode+"',BA.p00010001)>0 AND c.as_weight is not null and b.pardrg_code is not null and ipinfo.p00320000 < 61 and b.ver_code='"+drgVersion+"' \n");
		sql.append(" "+Arith.mrAROutDateSql(dateFrom,dateTo,"ipinfo")+" )dhc  group by dhc.codeupper,totalWeight,totalCseCnt,cnt \n");
		sql.append(" )b on b.codeupper = a.codeupper \n");
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ; 
		return list;
	}
	/**
	 * 附表2:获取所有手术科室
	 */
	public List<Map<String, Object>>  findOperDepList(Map<String, Object> map){
		String dateFrom = map == null?"":map.get("dateFrom").toString();
		String dateTo = map == null?"":map.get("dateTo").toString();
		String hospCode = map == null?"":map.get("hospCode").toString(); 
		StringBuffer sql=new StringBuffer();
		sql.append(" select distinct t.dep_code from drgs.mr_ipinfo a LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=a.PMAININDEX left join drgs.t_department t on a.p00300001=t.dep_code \n");
		sql.append(" where instr('"+hospCode+"',BA.p00010001)>0 AND t.is_ope_dep='1' "+Arith.mrAROutDateSql(dateFrom,dateTo,"a")+" \n");    
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ; 
		return list;
	}
	/**
	 * 附表2:获取手术科室DRGs各项指标
	 */
	public List<Map<String, Object>>  findOperDepDrgsListList(Map<String, Object> map,String depCode){
		String dateFrom = map == null?"":map.get("dateFrom").toString();
		String dateTo = map == null?"":map.get("dateTo").toString();
		String drgVersion = map == null?"":map.get("drgVersion").toString();
		String benchmarkYear = map == null?"":map.get("benchmarkYear").toString();
		String hospCode = map == null?"":map.get("hospCode").toString(); 
		StringBuffer sql=new StringBuffer();
		sql.append(" select a.p00300000 dep,pd.pardrg_code||'  '||drg.drg_desc drg,count(*) drgcnt,avg(std.as_weight) as_weight,count(*)*avg(std.as_weight) totalweight, \n");
		sql.append(" round(decode(COUNT(pd.PARID),0,0,SUM(decode(std.AS_AVG_DAYS,0,0,a.P00320000/std.AS_AVG_DAYS))/COUNT(pd.PARID)),2) AS timeeff, \n"); 
		sql.append(" round(decode(COUNT(pd.PARID),0,0,SUM(decode(std.AS_AVG_FEE,0,0,a.P00800000/std.AS_AVG_FEE))/COUNT(pd.PARID)),2) as feeeff \n");
		sql.append(" from drgs.mr_ipinfo a left join drgs.t_patientdrg pd on a.pmainindex=pd.parid LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=a.PMAININDEX \n");    
		sql.append(" left join drgs.t_drgs drg on drg.drg_code=pd.pardrg_code and pd.ver_code=drg.ver_code \n");
		sql.append(" left join drgs.t_annualstd std on std.as_drg_code=pd.pardrg_code and std.as_ID='"+benchmarkYear+"' \n");    
		sql.append(" where instr('"+hospCode+"',BA.p00010001)>0 AND a.p00300001='"+depCode+"' "+Arith.mrAROutDateSql(dateFrom,dateTo,"a")+" \n");
		sql.append(" and pd.ver_code='"+drgVersion+"' and pd.pardrg_code is not null group by a.p00300000,pd.pardrg_code||'  '||drg.drg_desc order by drgcnt desc \n");      
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ; 
		return list;
	}
	/**
	 * 附表3 全院 DRG组数和覆盖率
	 */
	@Override
	public List<Map<String, Object>> findHospallDRGsList(Map<String, Object> map) {
		String dateFrom = map == null?"":map.get("dateFrom").toString();
		String dateTo = map == null?"":map.get("dateTo").toString();
		String drgVersion = map == null?"":map.get("drgVersion").toString();
		String hospCode = map == null?"":map.get("hospCode").toString(); 
		StringBuffer sql=new StringBuffer();
		sql.append(" SELECT Y.drgTotalCount CNT,ROUND(decode(Y.drgTotalCount,0,0,X.drgsCount/Y.drgTotalCount),4)*100 RADIO FROM \n");
		sql.append(" (SELECT COUNT(DISTINCT A.PARDRG_CODE) drgsCount \n");    
		sql.append(" FROM DRGS.T_PATIENTDRG A, DRGS.MR_IPINFO B LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=b.PMAININDEX\n");
		sql.append(" WHERE instr('"+hospCode+"',BA.p00010001)>0 AND A.VER_CODE='"+drgVersion+"'  AND A.PARDRG_CODE is NOT NULL  AND  A.PARID = B.PMAININDEX  \n");
		sql.append(" "+Arith.mrAROutDateSql(dateFrom,dateTo,"B")+" ) X , \n");
		sql.append(" (select COUNT( ds.DRG_CODE ) drgTotalCount from drgs.t_drgs ds where ds.VER_CODE= '"+drgVersion+"' ) Y \n");
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ; 
		return list;
	}
	/**
	 * 附表3 各MDC专业DRG病组覆盖情况及医疗服务综合能力情况
	 */
	@Override
	public List<Map<String, Object>> findAllmdcDRGsList(Map<String, Object> map) {
		String dateFrom = map == null?"":map.get("dateFrom").toString();
		String dateTo = map == null?"":map.get("dateTo").toString();
		String drgVersion = map == null?"":map.get("drgVersion").toString();
		String benchmarkYear = map == null?"":map.get("benchmarkYear").toString();
		String hospCode = map == null?"":map.get("hospCode").toString(); 
		StringBuffer sql=new StringBuffer();
		sql.append(" SELECT  X.MDC CODE ,X.mdc_desc DECC,X.mdcincnt ACNT ,Y.drgTotalCount BCNT,ROUND(decode(Y.drgTotalCount,0,0,X.drgsCount/Y.drgTotalCount),4)*100 BRATIO,X.mdcmi CMI,X.drgsCount CCNT, X.weight WEIGHT, \n");
		sql.append(" X.mdctime TIME ,X.mdcfee FEE,X.risk1ratio,X.risk2ratio,risk3ratio,risk4ratio FROM \n");
		sql.append(" ( SELECT A.MDC, m.mdc_desc,count(A.PARID) mdcincnt, decode(count(A.PARID),0,0,sum(T.AS_WEIGHT)/count(A.PARID)) mdcmi, \n");
		sql.append(" sum(T.AS_WEIGHT) weight ,COUNT(DISTINCT A.PARDRG_CODE) drgsCount , \n");  
		sql.append(" round(decode(COUNT(A.PARID),0,0,SUM(decode(T.AS_AVG_DAYS,0,0,B.P00320000/T.AS_AVG_DAYS))/COUNT(A.PARID)),2) AS mdctime, \n");
		sql.append(" round(decode(COUNT(A.PARID),0,0,SUM(decode(T.AS_AVG_FEE,0,0,B.P00800000/T.AS_AVG_FEE))/COUNT(A.PARID)),2) as mdcfee , \n");
		sql.append(" ROUND(decode(sum(case WHEN T.AS_RISK_LEVEL='1' then 1 else 0 end),0,0,sum(case WHEN T.AS_RISK_LEVEL='1'AND B.P00620001='5' then 1 else 0 end)/sum(case WHEN T.AS_RISK_LEVEL='1' then 1 else 0 end)),4)*100 as risk1ratio, \n");
		sql.append(" ROUND(decode(sum(case WHEN T.AS_RISK_LEVEL='2' then 1 else 0 end),0,0,sum(case WHEN T.AS_RISK_LEVEL='2'AND B.P00620001='5' then 1 else 0 end)/sum(case WHEN T.AS_RISK_LEVEL='2' then 1 else 0 end)),4)*100 as risk2ratio, \n");
		sql.append(" ROUND(decode(sum(case WHEN T.AS_RISK_LEVEL='3' then 1 else 0 end),0,0,sum(case WHEN T.AS_RISK_LEVEL='3'AND B.P00620001='5' then 1 else 0 end)/sum(case WHEN T.AS_RISK_LEVEL='3' then 1 else 0 end)),4)*100 as risk3ratio, \n");
		sql.append(" ROUND(decode(sum(case WHEN T.AS_RISK_LEVEL='4' then 1 else 0 end),0,0,sum(case WHEN T.AS_RISK_LEVEL='4'AND B.P00620001='5' then 1 else 0 end)/sum(case WHEN T.AS_RISK_LEVEL='4' then 1 else 0 end)),4)*100 as risk4ratio \n");
		sql.append(" FROM DRGS.T_PATIENTDRG A, DRGS.T_ANNUALSTD T, DRGs.T_MDCGROUP m  ,DRGS.MR_IPINFO B,DRGS.MR_BASEINFO ba\n");
		sql.append(" WHERE instr('"+hospCode+"',BA.p00010001)>0 AND A.VER_CODE='"+drgVersion+"' and A.PARDRG_CODE = T.AS_DRG_CODE  AND T.AS_ID ='"+benchmarkYear+"'  \n");
		sql.append(" and a.ver_code = m.ver_code AND A.PARDRG_CODE is NOT NULL and t.as_mdc_code = m.mdc_code   AND  A.PARID = B.PMAININDEX AND B.P00320000 <61 and BA.PMAININDEX=b.PMAININDEX \n");
		sql.append(" "+Arith.mrAROutDateSql(dateFrom,dateTo,"B")+" \n");
		sql.append(" group by A.MDC, m.mdc_desc ) X LEFT JOIN \n");
		sql.append(" (select mg.mdc_code, count(DISTINCT drg.drg_code) drgTotalCount,mg.ver_code,mg.mdc_desc \n");
		sql.append(" from DRGs.t_Drgs drg, DRGs.t_Adrg adrg, DRGs.t_Mdcgroup mg  \n");
		sql.append(" WHERE drg.adrg_code = adrg.adrg_code \n");
		sql.append(" and adrg.mdc_code = mg.mdc_code \n");
		sql.append(" and adrg.VER_CODE=mg.ver_code and mg.ver_code=drg.ver_code and drg.ver_code='"+drgVersion+"' \n");
		sql.append(" group by mg.mdc_code,mg.ver_code,mg.mdc_desc )Y  ON X.MDC=Y.mdc_code \n");
		sql.append(" ORDER BY X.mdcincnt DESC \n");
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ; 
		return list;
	}
	/**
	 * 附表4  住院病例DRG总权重最高的二十位主治医师
	 */
	@Override
	public List<Map<String, Object>> findDocByWeightList(Map<String, Object> map) {
		String dateFrom = map == null?"":map.get("dateFrom").toString();
		String dateTo = map == null?"":map.get("dateTo").toString();
		String drgVersion = map == null?"":map.get("drgVersion").toString();
		String benchmarkYear = map == null?"":map.get("benchmarkYear").toString();
		String hospCode = map == null?"":map.get("hospCode").toString(); 
		StringBuffer sql=new StringBuffer();
		sql.append(" select  DISTINCT mr.P00600300 DECC ,mr.P00300000 DEP,SUM(A.AS_WEIGHT) WEIGHT,COUNT(DISTINCT p.PARDRG_CODE) DCNT,ROUND(decode(COUNT(P.PARID),0,0,SUM(A.AS_WEIGHT)/COUNT(P.PARID)),2) CMI , \n");
		sql.append(" round(decode(COUNT(P.PARID),0,0,SUM(decode(A.AS_AVG_DAYS,0,0,mr.P00320000/A.AS_AVG_DAYS))/COUNT(P.PARID)),2)  TIME, \n");
		sql.append(" round(decode(COUNT(P.PARID),0,0,SUM(decode(A.AS_AVG_FEE,0,0,mr.P00800000/A.AS_AVG_FEE))/COUNT(P.PARID)),2) FEE, \n");
		sql.append(" ROUND(decode(sum(case WHEN A.AS_RISK_LEVEL='1' then 1 else 0 end),0,0,sum(case WHEN A.AS_RISK_LEVEL='1'AND mr.P00620001='5' then 1 else 0 end)/sum(case WHEN A.AS_RISK_LEVEL='1' then 1 else 0 end)),4)*100 risk1ratio, \n");
		sql.append(" ROUND(decode(sum(case WHEN A.AS_RISK_LEVEL='2' then 1 else 0 end),0,0,sum(case WHEN A.AS_RISK_LEVEL='2'AND mr.P00620001='5' then 1 else 0 end)/sum(case WHEN A.AS_RISK_LEVEL='2' then 1 else 0 end)),4)*100 risk2ratio, \n");
		sql.append(" ROUND(decode(sum(case WHEN A.AS_RISK_LEVEL='3' then 1 else 0 end),0,0,sum(case WHEN A.AS_RISK_LEVEL='3'AND mr.P00620001='5' then 1 else 0 end)/sum(case WHEN A.AS_RISK_LEVEL='3' then 1 else 0 end)),4)*100 risk3ratio, \n");
		sql.append(" ROUND(decode(sum(case WHEN A.AS_RISK_LEVEL='4' then 1 else 0 end),0,0,sum(case WHEN A.AS_RISK_LEVEL='4'AND mr.P00620001='5' then 1 else 0 end)/sum(case WHEN A.AS_RISK_LEVEL='4' then 1 else 0 end)),4)*100 risk4ratio, \n");
		sql.append(" count(p.PARID) INCNT \n");
		sql.append(" from drgs.MR_IPINFO mr LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=mr.PMAININDEX\n");
		sql.append(" LEFT JOIN DRGS.T_PATIENTDRG P ON mr.PMAININDEX = P.PARID \n");
		sql.append(" LEFT JOIN DRGS.T_ANNUALSTD A  ON P.PARDRG_CODE = A.AS_DRG_CODE \n");
		sql.append(" where instr('"+hospCode+"',BA.p00010001)>0 AND mr.P00600300 IS NOT NULL AND A.AS_ID ='"+benchmarkYear+"' AND A.AS_WEIGHT IS not NULL  and p.ver_code='"+drgVersion+"' and P.PARDRG_CODE is not null  \n");
		sql.append(" "+Arith.mrAROutDateSql(dateFrom,dateTo,"mr")+" \n");
		sql.append(" GROUP BY mr.P00600300,mr.P00300000  ORDER BY weight DESC  \n");
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ; 
		return list;
	}
	/**
	 * 附表5 CMI值最高的二十位主治医师
	 */
	@Override
	public List<Map<String, Object>> findDocByCMIList(Map<String, Object> map) {
		String dateFrom = map == null?"":map.get("dateFrom").toString();
		String dateTo = map == null?"":map.get("dateTo").toString();
		String drgVersion = map == null?"":map.get("drgVersion").toString();
		String benchmarkYear = map == null?"":map.get("benchmarkYear").toString();
		String hospCode = map == null?"":map.get("hospCode").toString(); 
		StringBuffer sql=new StringBuffer();
		sql.append(" select X.physician DECC,X.P00300000 DEP, X.weight WEIGHT,X.DCNT DCNT, X.cmi CMI,X.time TIME, X.fee FEE, X.RISK1RATIO , X.RISK2RATIO ,X.RISK3RATIO , X.RISK4RATIO , X.INCNT INCNT \n");
		sql.append(" from (select DISTINCT mr.P00600300 physician  ,mr.P00300000 ,SUM(A.AS_WEIGHT) weight,COUNT(DISTINCT p.PARDRG_CODE) DCNT, ROUND(decode(COUNT(P.PARID),0,0,SUM(A.AS_WEIGHT)/COUNT(P.PARID)),2) CMI , \n");
		sql.append(" round(decode(COUNT(P.PARID),0,0,SUM(decode(A.AS_AVG_DAYS,0,0,mr.P00320000/A.AS_AVG_DAYS))/COUNT(P.PARID)),2)  TIME, \n");
		sql.append(" round(decode(COUNT(P.PARID),0,0,SUM(decode(A.AS_AVG_FEE,0,0,mr.P00800000/A.AS_AVG_FEE))/COUNT(P.PARID)),2) FEE , \n");
		sql.append(" ROUND(decode(sum(case WHEN A.AS_RISK_LEVEL='1' then 1 else 0 end),0,0,sum(case WHEN A.AS_RISK_LEVEL='1'AND mr.P00620001='5' then 1 else 0 end)/sum(case WHEN A.AS_RISK_LEVEL='1' then 1 else 0 end)),4)*100 RISK1RATIO, \n");
		sql.append(" ROUND(decode(sum(case WHEN A.AS_RISK_LEVEL='2' then 1 else 0 end),0,0,sum(case WHEN A.AS_RISK_LEVEL='2'AND mr.P00620001='5' then 1 else 0 end)/sum(case WHEN A.AS_RISK_LEVEL='2' then 1 else 0 end)),4)*100 RISK2RATIO, \n");
		sql.append(" ROUND(decode(sum(case WHEN A.AS_RISK_LEVEL='3' then 1 else 0 end),0,0,sum(case WHEN A.AS_RISK_LEVEL='3'AND mr.P00620001='5' then 1 else 0 end)/sum(case WHEN A.AS_RISK_LEVEL='3' then 1 else 0 end)),4)*100 RISK3RATIO, \n");
		sql.append(" ROUND(decode(sum(case WHEN A.AS_RISK_LEVEL='4' then 1 else 0 end),0,0,sum(case WHEN A.AS_RISK_LEVEL='4'AND mr.P00620001='5' then 1 else 0 end)/sum(case WHEN A.AS_RISK_LEVEL='4' then 1 else 0 end)),4)*100 RISK4RATIO, \n");
		sql.append(" count(p.PARID) INCNT \n");
		sql.append(" from drgs.MR_IPINFO mr LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=mr.PMAININDEX\n");
		sql.append(" LEFT JOIN DRGS.T_PATIENTDRG P ON mr.PMAININDEX = P.PARID \n");
		sql.append(" LEFT JOIN DRGS.T_ANNUALSTD A  ON P.PARDRG_CODE = A.AS_DRG_CODE \n");
		sql.append(" where instr('"+hospCode+"',BA.p00010001)>0 AND mr.P00600300 IS NOT NULL AND A.AS_ID ='"+benchmarkYear+"' AND A.AS_WEIGHT IS not NULL  and p.ver_code='"+drgVersion+"'  and P.PARDRG_CODE is not null  \n");
		sql.append(" "+Arith.mrAROutDateSql(dateFrom,dateTo,"mr")+" \n");
		sql.append(" GROUP BY mr.P00600300,mr.P00300000 ORDER BY CMI DESC  ) X \n");
		sql.append(" where X.incnt>=10 \n");
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ; 
		return list;
	}
	/**
	 *附表6 未入组病例覆盖的所有主要诊断
	 */
	@Override
	public List<Map<String, Object>> findOldNotInGroupDiagList(Map<String, Object> map) {
		String dateFrom = map == null?"":map.get("dateFrom").toString();
		String dateTo = map == null?"":map.get("dateTo").toString();
		String drgVersion = map == null?"":map.get("drgVersion").toString();
		String hospCode = map == null?"":map.get("hospCode").toString(); 
		StringBuffer sql=new StringBuffer();
		sql.append(" SELECT X.PD0101 CODE, X.PD0100 DECC, X.cnt CNT,ROUND(decode(Y.allcnt,0,0,X.cnt/Y.allcnt*100),2) RATIO \n");
		sql.append(" from (select de.PD0101, de.PD0100 ,count(1) cnt from drgs.MR_IPINFO m left join drgs.MR_DIAGNOSE de on m.PMAININDEX=de.PMAININDEX \n");
		sql.append(" LEFT JOIN drgs.t_patientdrg p on m.PMAININDEX=p.PARID LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=m.PMAININDEX\n");
		sql.append(" where instr('"+hospCode+"',BA.p00010001)>0 AND de.pdtype='M' AND  m.P00320000<61 and p.PARDRG_CODE is null and p.VER_CODE= '"+drgVersion+"' \n");
		sql.append(" "+Arith.mrAROutDateSql(dateFrom,dateTo,"m")+" \n");
		sql.append(" GROUP BY de.PD0101, de.PD0100 ORDER BY cnt DESC ) X, \n");
		sql.append(" (select count(1) allcnt from drgs.MR_IPINFO m LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=m.PMAININDEX \n");
		sql.append(" LEFT JOIN drgs.t_patientdrg p on m.PMAININDEX=p.PARID \n");
		sql.append(" where instr('"+hospCode+"',BA.p00010001)>0 AND m.P00320000<61 and p.PARDRG_CODE is null  \n");
		sql.append(" "+Arith.mrAROutDateSql(dateFrom,dateTo,"m")+" \n");
		sql.append(" and p.VER_CODE='"+drgVersion+"' )Y \n");
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ; 
		return list;
	}
	/**
	 *附表8 内科组/外科组/操作组病组明细
	 * @param map
	 * @return
	 */
	public List<Map<String, Object>>  findDRGTypeCntList(Map<String, Object> map){
		String dateFrom = map == null?"":map.get("dateFrom").toString();
		String dateTo = map == null?"":map.get("dateTo").toString();
		String drgVersion = map == null?"":map.get("drgVersion").toString();
		String hospCode = map == null?"":map.get("hospCode").toString(); 
		StringBuffer sql=new StringBuffer();
		sql.append(" SELECT * FROM (select Wdrgcode,Wdrgdesc,Wcnt,rownum rn from (SELECT DISTINCT p.PARDRG_CODE  as Wdrgcode,drg.drg_desc Wdrgdesc,count(*)Wcnt from drgs.MR_IPINFO mr LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=mr.PMAININDEX \n");
		sql.append(" LEFT JOIN drgs.t_patientdrg p on mr.PMAININDEX=p.PARID left join drgs.t_drgs drg on drg.ver_code=p.ver_code and drg.drg_code=p.pardrg_code \n");
		sql.append(" where instr('"+hospCode+"',BA.p00010001)>0 AND p.PARDRG_CODE is not null and p.VER_CODE='"+drgVersion+"' "+Arith.mrAROutDateSql(dateFrom,dateTo,"mr")+" \n");
		sql.append(" and (SUBSTR(p.PARDRG_CODE,2,1)='A' OR SUBSTR(p.PARDRG_CODE,2,1)='B' OR SUBSTR(p.PARDRG_CODE,2,1)='C' \n");
		sql.append(" OR SUBSTR(p.PARDRG_CODE,2,1)='D' OR SUBSTR(p.PARDRG_CODE,2,1)='E' OR SUBSTR(p.PARDRG_CODE,2,1)='F' OR SUBSTR(p.PARDRG_CODE,2,1)='G' \n");
		sql.append(" OR SUBSTR(p.PARDRG_CODE,2,1)='H' OR SUBSTR(p.PARDRG_CODE,2,1)='J') group by p.pardrg_code,drg.drg_desc order by Wcnt desc)where rownum<11) A LEFT JOIN \n");
		sql.append(" (select Ndrgcode,Ndrgdesc,Ncnt,rownum rn from (SELECT DISTINCT p.PARDRG_CODE  as Ndrgcode,drg.drg_desc Ndrgdesc,count(*)Ncnt from drgs.MR_IPINFO mr LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=mr.PMAININDEX \n");
		sql.append(" LEFT JOIN drgs.t_patientdrg p on mr.PMAININDEX=p.PARID left join drgs.t_drgs drg on drg.ver_code=p.ver_code and drg.drg_code=p.pardrg_code \n");
		sql.append(" where instr('"+hospCode+"',BA.p00010001)>0 AND p.PARDRG_CODE is not null and p.VER_CODE='"+drgVersion+"'  "+Arith.mrAROutDateSql(dateFrom,dateTo,"mr")+" \n");
		sql.append(" and ( SUBSTR(p.PARDRG_CODE,2,1)='R' OR SUBSTR(p.PARDRG_CODE,2,1)='S' OR SUBSTR(p.PARDRG_CODE,2,1)='T' OR SUBSTR(p.PARDRG_CODE,2,1)='U' \n");
		sql.append(" OR SUBSTR(p.PARDRG_CODE,2,1)='V' OR SUBSTR(p.PARDRG_CODE,2,1)='W' OR SUBSTR(p.PARDRG_CODE,2,1)='X' \n");
		sql.append(" OR SUBSTR(p.PARDRG_CODE,2,1)='Y' OR SUBSTR(p.PARDRG_CODE,2,1)='Z') \n");
		sql.append(" group by p.pardrg_code,drg.drg_desc order by Ncnt desc)where rownum<11)B ON a.rn=b.rn LEFT JOIN \n");
		sql.append(" (select Cdrgcode,cdrgdesc,Ccnt,rownum rn from (SELECT DISTINCT p.PARDRG_CODE  as Cdrgcode,drg.drg_desc cdrgdesc, count(*)Ccnt from drgs.MR_IPINFO mr LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=mr.PMAININDEX \n");
		sql.append(" LEFT JOIN drgs.t_patientdrg p on mr.PMAININDEX=p.PARID left join drgs.t_drgs drg on drg.ver_code=p.ver_code and drg.drg_code=p.pardrg_code \n");
		sql.append(" where instr('"+hospCode+"',BA.p00010001)>0 AND p.PARDRG_CODE is not null and p.VER_CODE='"+drgVersion+"' "+Arith.mrAROutDateSql(dateFrom,dateTo,"mr")+" \n");
		sql.append(" and ( SUBSTR(p.PARDRG_CODE,2,1)='K' OR SUBSTR(p.PARDRG_CODE,2,1)='L' OR SUBSTR(p.PARDRG_CODE,2,1)='M' \n");
		sql.append(" OR SUBSTR(p.PARDRG_CODE,2,1)='N' OR SUBSTR(p.PARDRG_CODE,2,1)='P' OR SUBSTR(p.PARDRG_CODE,2,1)='Q') \n");
		sql.append(" group by p.pardrg_code,drg.drg_desc order by Ccnt desc)where rownum<11)c on a.rn=c.rn order by a.rn \n");
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ; 
		return list;
	}
	
	/**
	 *附表9 死亡病例明细
	 * @param map
	 * @return
	 */
	public List<Map<String, Object>>  findDeadCntList(Map<String, Object> map){
		String dateFrom = map == null?"":map.get("dateFrom").toString();
		String dateTo = map == null?"":map.get("dateTo").toString();
		String drgVersion = map == null?"":map.get("drgVersion").toString();
		String benchmarkYear = map == null?"":map.get("benchmarkYear").toString();
		String hospCode = map == null?"":map.get("hospCode").toString(); 
		StringBuffer sql=new StringBuffer();
		sql.append(" SELECT p00050000 BAH,p00060000 NAME,TO_CHAR(p00290000,'YYYY-MM-DD') OUTDATE,p00300000 OUTDEP,p00320000 INDAYS, \n");
		sql.append(" p00800000 FEE,pd0100 MAINDIA,P.PARDRG_CODE DRGCODE,DRG.DRG_DESC DRGDESC,ANN.AS_WEIGHT,ANN.AS_AVG_DAYS,ANN.AS_AVG_FEE, \n");
		sql.append(" decode(ANN.AS_RISK_LEVEL,'0','零风险','1','低','2','中低','3','中高','高') RISK_LEVEL from drgs.MR_IPINFO mr \n");
		sql.append(" LEFT JOIN DRGS.MR_BASEINFO B ON MR.PMAININDEX=B.PMAININDEX \n");
		sql.append(" LEFT JOIN DRGS.MR_DIAGNOSE DIA ON DIA.PMAININDEX=MR.PMAININDEX AND DIA.PDTYPE='M' \n");
		sql.append(" LEFT JOIN drgs.t_patientdrg p on mr.PMAININDEX=p.PARID and p.PARDRG_CODE is not null and p.VER_CODE='"+drgVersion+"'\n");
		sql.append(" left join drgs.t_drgs drg on drg.ver_code=p.ver_code and drg.drg_code=p.pardrg_code \n");
		sql.append(" LEFT JOIN DRGS.T_ANNUALSTD ANN ON ANN.AS_DRG_CODE=P.PARDRG_CODE AND ANN.AS_ID='"+benchmarkYear+"' \n");
		sql.append(" where instr('"+hospCode+"',B.p00010001)>0 \n");
		sql.append(" "+Arith.mrAROutDateSql(dateFrom,dateTo,"mr")+" AND MR.p00620001='5' \n");
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ; 
		return list;
	}
}
